lunes, 26 de marzo de 2018

Ejecutar URL y enviar variable a PHP desde SQL SERVER

Cuando necesitamos ejecutar un codigo PHP desde SQL y además enviarle variables

-- declaramos una variable cadena y ponemos la url a invocar
Declare @php varchar(max)='http://URL/ARCHIVO.php?item='+@VARIABLE
-- declaramos una variable entero, para guardar el id del objeto OLE que crearemos
Declare @Objecto as Int;
-- Una variable cadena para la respuesta
Declare @ResponseText as Varchar(8000);

-- creamos un objeto OLE
Exec sp_OACreate 'MSXML2.XMLHTTP', @Objecto OUT;
-- Ejecutamos la url por medio de post
Exec sp_OAMethod @Objecto, 'open', NULL, 'post',@php,'false'
Exec sp_OAMethod @Objecto, 'send'
Exec sp_OAMethod @Objecto, 'responseText', @ResponseText OUTPUT

-- imprimimos resultado
Select @ResponseText

--eliminamos nuestro id de objeto OLE
Exec sp_OADestroy @Objecto

viernes, 23 de marzo de 2018

SQL server { Paginación Resultados } OFFSET FETCH NEXT

OFFSET {número inicial} ROWS FETCH NEXT { cantidad de registros a mostrar desde el inicial } ROWS ONLY

Ejemplo:  5 registros a partir del registro 10
SELECT Id, ProductName, UnitPrice, Package
FROM Product
ORDER BY UnitPrice DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY

Listado de tipos de objeto (Object Types) en SAP BUSSINES ONE

TableTable descriptionPrimary KeyObject Type
OACTG/L AccountsAcctCode1
OCRDBusiness PartnerCardCode2
ODSCBank CodesAbsEntry3
OITMItemsItemCode4
OVTGTax DefinitionCode5
OPLNPrice ListsListNum6
OSPPSpecial PricesCardCode, ItemCode7
OITGItem PropertiesItmsTypCod8
ORTMRate DifferencesLineNum, IsSysCurr9
OCRGCard GroupsGroupCode10
OCPRContact PersonsCntctCode11
OUSRUsersUSERID12
OINVA/R InvoiceDocEntry13
ORINA/R Credit MemoDocEntry14
ODLNDeliveryDocEntry15
ORDNReturnsDocEntry16
ORDRSales OrderDocEntry17
OPCHA/P InvoiceDocEntry18
ORPCA/P Credit MemoDocEntry19
OPDNGoods Receipt PODocEntry20
ORPDGoods ReturnDocEntry21
OPORPurchase OrderDocEntry22
OQUTSales QuotationDocEntry23
ORCTIncoming PaymentDocEntry24
ODPSDepositDeposId25
OMTHReconciliation HistoryMthAcctCod, IsInternal, MatchNum26
OCHHCheck RegisterCheckKey27
OBTFJournal Voucher EntryBatchNum, TransId28
OBTDJournal Vouchers ListBatchNum29
OJDTJournal EntryTransId30
OITWItems – WarehouseItemCode, WhsCode31
OADPPrint PreferencesPrintId32
OCLGActivitiesClgCode33
ORCRRecurring PostingsRcurCode, Instance34
ONNMDocument NumberingObjectCode, DocSubType35
OCRCCredit CardsCreditCard36
OCRNCurrency CodesCurrCode37
OIDXCPI CodesIdexCode38
OADMAdministrationCode39
OCTGPayment TermsGroupNum40
OPRFPreferencesFormNumber, UserSign41
OBNKExternal Bank Statement ReceivedAcctCode, Sequence42
OMRCManufacturersFirmCode43
OCQGCard PropertiesGroupCode44
OTRCJournal Entry CodesTrnsCode45
OVPMOutgoing PaymentsDocEntry46
OSRLSerial NumbersItemCode, SerialNum47
OALCLoading ExpensesAlcCode48
OSHPDelivery TypesTrnspCode49
OLGTLength UnitsUnitCode50
OWGTWeight UnitsUnitCode51
OITBItem GroupsItmsGrpCod52
OSLPSales EmployeeSlpCode53
OFLTReport – Selection CriteriaFormNum, UserSign, FilterName54
OTRTPosting TemplatesTrtCode55
OARGCustoms GroupsCstGrpCode56
OCHOChecks for PaymentCheckKey57
OINMWhse JournalTransNum, Instance58
OIGNGoods ReceiptDocEntry59
OIGEGoods IssueDocEntry60
OPRCCost CenterPrcCode61
OOCRCost RateOcrCode62
OPRJProject CodesPrjCode63
OWHSWarehousesWhsCode64
OCOGCommission GroupsGroupCode65
OITTProduct TreeCode66
OWTRInventory TransferDocEntry67
OWKOProduction InstructionsOrderNum68
OIPFLanded CostsDocEntry69
OCRPPayment MethodsCrTypeCode70
OCDTCredit Card PaymentCode71
OCRHCredit Card ManagementAbsId, Instance72
OSCNCustomer/Vendor Cat. No.ItemCode, CardCode, Substitute73
OCRVCredit PaymentsAbsId, PayId, Instance74
ORTTCPI and FC RatesRateDate, Currency75
ODPTPostdated DepositDeposId76
OBGTBudgetAbsId77
OBGDBudget Cost Assess. MthdBgdCode78
ORCNRetail ChainsChainCode79
OALTAlerts TemplateCode80
OALRAlertsCode81
OAIBReceived AlertsAlertCode, UserSign82
OAOBMessage SentAlertCode, UserSign83
OCLSActivity SubjectsCode84
OSPGSpecial Prices for GroupsCardCode, ObjType, ObjKey85
SPRGApplication StartLineNum, UserCode86
OMLSDistribution ListCode87
OENTShipping TypesDocEntry88
OSALOutgoingDocEntry89
OTRATransitionDocEntry90
OBGSBudget ScenarioAbsId91
OIRTInterest PricesNumerator92
OUDGUser DefaultsCode93
OSRISerial Numbers for ItemsItemCode, SysSerial94
OFRTFinancial Report TemplatesAbsId95
OFRCFinancial Report CategoriesTemplateId, CatId96
OOPROpportunityOpprId97
OOINInterestNum98
OOIRInterest LevelNum99
OOSRInformation SourceNum100
OOSTOpportunity StageNum101
OOFRDefect CauseNum102
OCLTActivity TypesCode103
OCLOMeetings LocationCode104
OISRService CallsRequestNum105
OIBTBatch No. for ItemItemCode, BatchNum, WhsCode106
OALIAlternative Items 2OrigItem, AltItem107
OPRTPartnersPrtId108
OCMTCompetitorsCompetId109
OUVVUser ValidationsIndexID, LineNum110
OFPRPosting PeriodAbsEntry111
ODRFDraftsDocEntry112
OSRDBatches and Serial NumbersItemCode, DocType, DocEntry, DocLineNum113
OUDCUser Display Cat.CodeID114
OPVLLender – PelecardCode115
ODDTWithholding Tax Deduction HierarchyNumerator116
ODDGWithholding Tax Deduction GroupsNumerator117
OUBRBranchesCode118
OUDPDepartmentsCode119
OWSTConfirmation LevelWstCode120
OWTMApproval TemplatesWtmCode121
OWDDDocs. for ConfirmationWddCode122
OCHDChecks for Payment DraftsCheckKey123
CINFCompany InfoVersion124
OEXDFreight SetupExpnsCode125
OSTASales Tax AuthoritiesCode, Type126
OSTTSales Tax Authorities TypeAbsId127
OSTCSales Tax CodesCode128
OCRYCountriesCode129
OCSTStatesCountry, Code130
OADFAddress FormatsCode131
OCINA/R Correction InvoiceDocEntry132
OCDCCash DiscountCode133
OQCNQuery CatagoriesCategoryId134
OINDTriangular DealCode135
ODMWData MigrationCode136
OCSTNWorkstation IDCode137
OIDCIndicatorCode138
OGSPGoods ShipmentCode139
OPDFPayment DraftDocEntry140
OQWZQuery WizardCode141
OASGAccount SegmentationAbsId142
OASCAccount Segmentation CategoriesSegmentId, Code143
OLCTLocationCode144
OTNN1099 FormsFormCode145
OCYCCycleCode146
OPYMPayment Methods for Payment WizardPayMethCod147
OTOB1099 Opening BalanceVendCode, Form1099, Box1099148
ORITDunning Interest RateCode149
OBPPBP PrioritiesPrioCode150
ODUNDunning LettersLineNum151
CUFDUser Fields – DescriptionTableID, FieldID152
OUTBUser TablesTableName153
OCUMIMy Menu ItemsUserSign , Id_154
OPYDPayment RunCode155
OPKLPick ListAbsEntry156
OPWZPayment WizardIdNumber157
OPEXPayment Results TableAbsEntry158
OPYBPayment BlockAbsEntry159
OUQRQueriesIntrnalKey, Qcategory160
OCBICentral Bank Ind.Indicator161
OMRVInventory RevaluationDocEntry162
OCPIA/P Correction InvoiceDocEntry163
OCPVA/P Correction Invoice ReversalDocEntry164
OCSIA/R Correction InvoiceDocEntry165
OCSVA/R Correction Invoice ReversalDocEntry166
OSCSService Call StatusesstatusID167
OSCTService Call TypescallTypeID168
OSCPService Call Problem TypesprblmTypID169
OCTTContract TemplateTmpltName170
OHEMEmployeesempID171
OHTYEmployee TypestypeID172
OHSTEmployee StatusstatusID173
OHTRTermination ReasonreasonID174
OHEDEducation TypesedType175
OINSCustomer Equipment CardinsID176
OAGPAgent NameAgentCode177
OWHTWithholding TaxWTCode178
ORFLAlready Displayed 347, 349 and WTax ReportsDocEntry, ReportType, DocType, LineNum, TaxCode, OrdinalNum179
OVTRTax ReportAbsEntry180
OBOEBill of Exchange for PaymentBoeKey181
OBOTBill Of Exchang TransactionAbsEntry182
OFRMFile FormatAbsEntry183
OPIDPeriod IndicatorIndicator184
ODORDoubtful DebtsAbsEntry185
OHLDHoliday TableHldCode186
OCRBBP – Bank AccountCountry, BankCode, Account, CardCode187
OSSTService Call Solution StatusesNumber188
OSLTService Call SolutionsSltCode189
OCTRService ContractsContractID190
OSCLService CallscallID191
OSCOService Call OriginsoriginID192
OUKDUser Key DescriptionTableName, KeyId193
OQUEQueuequeueID194
OIWZInflation WizardAbsEntry195
ODUTDunning TermsTermCode196
ODWZDunning WizardWizardId197
OFCTSales ForecastAbsID198
OMSNMRP ScenariosAbsEntry199
OTERTerritoriesterritryID200
OONDIndustriesIndCode201
OWORProduction OrderDocEntry202
ODPIA/R Down PaymentDocEntry203
ODPOA/P Down PaymentDocEntry204
OPKGPackage TypesPkgCode205
OUDOUser-Defined ObjectCode206
ODOWData Ownership – ObjectsObject, SubObject207
ODOXData Ownership – ExceptionsQueryId, Object, SubObject208
209
OHPSEmployee PositionposID210
OHTMEmployee TeamsteamID211
OORLRelationshipsOrlCode212
ORCMRecommendation DataDocEntry213
OUPTUser Autorization TreeAbsId214
OPDTPredefined TextAbsEntry215
OBOXBox DefinitionBoxCode, ReportType, BosCode216
OCLAActivity StatusstatusID217
OCHF312ObjName218
OCSHSUser-Defined ValuesIndexID219
OACPPeriods CategoryAbsEntry220
OATCAttachmentsAbsEntry221
OGFLGrid FilterFormID, GridID, UserCode222
OLNGUser Language TableCode223
OMLTMulti-Language TranslationTranEntry224
OAPA3225
OAPA4226
OAPA5227
SDISDynamic Interface (Strings)FormId, ItemId, ColumnId, Language229
OSVRSaved ReconciliationsacctCode230
DSC1House Bank AccountsAbsEntry231
RDOCDocumentDocCode232
ODGPDocument Generation Parameter SetsAbsEntry233
OMHD#740AlertCode234
OACGAccount CategoryAbsId238
OBCABank Charges Allocation CodesCode239
OCFTCash Flow Transactions – RowsCFTId241
OCFWCash Flow Line ItemCFWId242
OBPLBusiness PlaceBPLId247
OJPELocal Era CalendarCode250
ODIMCost Accounting DimensionDimCode251
OSCDService Code TableAbsEntry254
OSGPService Group for BrazilAbsEntry255
OMGPMaterial GroupAbsEntry256
ONCMNCM CodeAbsEntry257
OCFPCFOP for Nota FiscalID258
OTSCCST Code for Nota FiscalID259
OUSGUsage of Nota FiscalID260
OCDPClosing Date ProcedureClsDateNum261
ONFNNota Fiscal NumberingObjectCode, DocSubType263
ONFTNota Fiscal Tax Category (Brazil)AbsId264
OCNTCountiesAbsId265
OTCDTax Code DeterminationAbsId266
ODTYBoE Document TypeAbsEntry267
OPTFBoE PortfolioAbsEntry268
OISTBoE InstructionAbsEntry269
OTPSTax ParameterAbsId271
OTFCTax Type CombinationAbsId275
OFMLTax Formula Master TableAbsId276
OCNACNAE CodeAbsId278
OTSISales Tax InvoiceDocEntry280
OTPIPurchase Tax InvoiceDocEntry281
OCCDCargo Customs Declaration NumbersCCDNum283
ORSCResourcesResCode290
ORSGResource PropertiesResTypCod291
ORSBResGrpCodResGrpCod292
RecordSet300
Bridge305
OITRInternal ReconciliationReconNum321
OPOSPOS Master DataEquipNo541
ODRFStock Transfer DraftDocEntry1179
OMSGMessaging Service SettingsUSERID10000105
OBTNBatch Numbers Master DataAbsEntry10000044
OSRNSerial Numbers Master DataAbsEntry10000045
OIVKIVL Vs OINM KeysTransSeq10000062
OIQRInventory PostingDocEntry10000071
OFYMFinancial Year MasterAbsId10000073
OSECSectionsAbsId10000074
OCSNCertificate SeriesAbsId10000075
ONOANature of AssesseeAbsId10000077
RTYPDocument Type ListCODE10000196
OUGPUoM GroupUgpEntry10000197
OUOMUoM Master DataUomEntry10000199
OBFCBin Field ConfigurationAbsEntry10000203
OBATBin Location AttributeAbsEntry10000204
OBSLWarehouse SublevelAbsEntry10000205
OBINBin LocationAbsEntry10000206
ODNFDNF CodeAbsEntry140000041
OUGRAuthorization GroupGroupId231000000
OEGPE-Mail GroupEmlGrpCode234000004
OGPCGovernment Payment CodeAbsId243000001
OIQIInventory Opening BalanceDocEntry310000001
OBTWBatch Attributes in LocationAbsEntry310000008
OLLFLegal List FormatAbsEntry410000005
OHETObject: HR Employee TransferTransferID480000001
OTCXTax Code DeterminationDocEntry540000005
OPQTPurchase QuotationDocEntry540000006
ORCPRecurring Transaction TemplateAbsEntry540000040
OCCTCost Center TypeCctCode540000042
OACRAccrual TypeCode540000048
ONFMNota Fiscal ModelAbsEntry540000056
OBFIBrazil Fuel IndexerID540000067
OBBIBrazil Beverage IndexerID540000068
OCPTCockpit Main TableAbsEntry1210000000
OWTQInventory Transfer RequestDocEntry1250000001
OOATBlanket AgreementAbsID1250000025
OKPIKey Performance Indicator PackageAbsEntry1320000000
OTGGTarget GroupTargetCode1320000002
OCPNCampaignCpnNo1320000012
OROCRetorno Operation CodesAbsEntry1320000028
OPSCProduct Source CodeCode1320000039
ODTPFixed Assets Depreciation TypesCode1470000000
OADTFixed Assets Account DeterminationCode1470000002
ODPAFixed Asset Depreciation AreasCode1470000003
ODPPDepreciation Type PoolsCode1470000004
OACSAsset ClassesCode1470000032
OAGSAsset GroupsCode1470000046
ODMCG/L Account Determination Criteria – InventoryDmcId1470000048
OACQCapitalizationDocEntry1470000049
OGARG/L Account Advanced RulesAbsEntry1470000057
OACDCredit MemoDocEntry1470000060
OBCDBar Code Master DataBcdEntry1470000062
OINCInventory CountingDocEntry1470000065
OEDGDiscount GroupsAbsEntry1470000077
OCCSCycle Count DeterminationWhsCode1470000092
OPRQPurchase RequestDocEntry1470000113
OWLSWorkflow – Task DetailsTaskID1620000000