This page lists down the common sqls used in regular OMS support activity.
1). How to generate multi api input XML with changeOrder to resolve hold for 100
orders at one go?
select '<MultiApi>' from sysibm.sysdummy1
union
SELECT
DISTINCT '<API Name="changeOrder"> <Input> <Order OrderHeaderKey="' || trim(OH.ORDER_HEADER_KEY)
|| '" Action="MODIFY"> <OrderHoldTypes><OrderHoldType HoldType="' || HLD.HOLD_TYPE || '"
ReasonText="Incident" Status="1300"/></OrderHoldTypes></Order></Input></API>'
FROM
STERLING.YFS_ORDER_HEADER OH,
STERLING.YFS_ORDER_HOLD_TYPE HLD
WHERE
AND OH.ORDER_HEADER_KEY = HLD.ORDER_HEADER_KEY
AND HLD.HOLD_TYPE = '<hold_type>'
AND OH.ORDER_HEADER_KEY > '20240401'
AND HLD.STATUS = '1100'
AND OH.ORDER_NO IN ('1122334455','6677889900')
AND SELLER_ORGANIZATION_CODE = '50708'
AND OH.ENTERPRISE_KEY = 'ENTP'
union
select '</MultiApi>' from sysibm.sysdummy1
*Please use 'dual' in plac of 'sysibm. sysdummy1' for Oracle DB
2). Can you please provide the Payment related queries.
SELECT * FROM STERLING.YFS_CHARGE_TRANSACTION WHERE ORDER_HEADER_KEY IN (
SELECT ORDER_HEADER_KEY FROM STERLING.YFS_ORDER_HEADER WHERE ORDER_NO = '888899990000');
SELECT * FROM STERLING.YFS_CREDIT_CARD_TRANSACTION WHERE CHARGE_TRANSACTION_KEY IN (
SELECT CHARGE_TRANSACTION_KEY FROM STERLING.YFS_CHARGE_TRANSACTION WHERE ORDER_HEADER_KEY IN (
SELECT ORDER_HEADER_KEY FROM STERLING.YFS_ORDER_HEADER WHERE ORDER_NO IN ('77778888999')));
SELECT * FROM STERLING.YFS_CHARGE_TRAN_REQUEST WHERE ORDER_HEADER_KEY IN (
SELECT ORDER_HEADER_KEY FROM STERLING.YFS_ORDER_HEADER WHERE ORDER_NO IN ('77778888999')));
*
3). How to generate multi api input XML with changeOrder to SUSPEND a Payment Type (tender) for 100
orders at one go?
select '<MultiApi>' from sysibm.sysdummy1
union
SELECT
DISTINCT '<API Name="changeOrder"> <Input> <Order OrderHeaderKey="' || trim(OH.ORDER_HEADER_KEY) || '" Action="MODIFY">
<PaymentMethods> <PaymentMethod Action="MODIFY" PaymentKey="' || trim(PMT.PAYMENT_KEY) ||
'" SuspendAnyMoreCharges="B" PaymentType="'|| trim(PMT.PAYMENT_TYPE) || '" /> </PaymentMethods>
</Order> </Input> </API>'
FROM
STERLING.YFS_ORDER_HEADER OH,
STERLING.YFS_PAYMENT PMT
WHERE
AND OH.ORDER_HEADER_KEY = PMT.ORDER_HEADER_KEY
AND OH.ORDER_HEADER_KEY > '20240101'
AND OH.ORDER_HEADER_KEY < '20240331'
AND PMT.PAYMENT_TYPE = 'Credit'
AND OH.ORDER_NO IN ('1122334455','334455667788')
AND OH.ENTERPRISE_KEY='ENTP';
union
select '</MultiApi>' from sysibm.sysdummy1
*Please use 'dual' in plac of 'sysibm. sysdummy1' for Oracle DB
4). How to ORDER LINE AND RELEASE INFORMATION FOR AN ORDER?
SELECT OH.*, OL.*, RS.*
FROM
STERLING.YFS_ORDER_HEADER OH,
STERLING.YFS_ORDER_RELEASE_STATUS ORS,
STERLING.YFS_ORDER_LINE OL
WHERE
OH.ORDER_HEADER_KEY = ORS.ORDER_HEADER_KEY
AND OH.ORDER_HEADER_KEY = OL.ORDER_HEADER_KEY
AND OL.ORDER_LINE_KEY = OL.ORDER_LINE_KEY
AND OH.ENTERPRISE_KEY = 'ENTP'
AND OH.ORDER_HEADER_KEY > '20220101'
AND OH.ORDER_HEADER_KEY < '20230101'
AND OH.ORDER_NO IN (
'4444444444',
8888888888 ');
*Please use 'dual' in plac of 'sysibm. sysdummy1' for Oracle DB