[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
SOB.NAME,
SOB.CURRENCY_CODE
INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
FROM
GL_SETS_OF_BOOKS SOB,
FND_CURRENCIES CUR
WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
SELECT
CP.USER_CONCURRENT_PROGRAM_NAME
INTO L_REPORT_NAME
FROM
FND_CONCURRENT_PROGRAMS_VL CP,
FND_CONCURRENT_REQUESTS CR
WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
SELECT
MEANING
INTO L_SHIP
FROM
OE_LOOKUPS
WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
AND LOOKUP_CODE = L_LOOKUP_CODE;
SELECT
MEANING
INTO L_ORD
FROM
OE_LOOKUPS
WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
AND LOOKUP_CODE = L_LOOKUP_CODE;
SELECT
MEANING
INTO L_PICK
FROM
OE_LOOKUPS
WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
AND LOOKUP_CODE = L_LOOKUP_CODE;
SELECT
MEANING
INTO L_PACK
FROM
OE_LOOKUPS
WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
AND LOOKUP_CODE = L_LOOKUP_CODE;
SELECT
OEOT.NAME
INTO L_ORDER_TYPE
FROM
OE_TRANSACTION_TYPES_TL OEOT
WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE
AND OEOT.LANGUAGE = USERENV('LANG');
SELECT
NVL(SUM(NVL(CPA.OVERALL_CREDIT_LIMIT
,-1) * (100 + CP.TOLERANCE) / 100)
,-1),
NVL(SUM(NVL(CPA.TRX_CREDIT_LIMIT
,-1) * (100 + CP.TOLERANCE) / 100)
,-1)
INTO L_TOTAL_LIMIT,L_ORDER_LIMIT
FROM
HZ_CUSTOMER_PROFILES CP,
HZ_CUST_PROFILE_AMTS CPA
WHERE CP.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
AND CPA.CURRENCY_CODE = CURRENCY1;
SELECT
NVL(SUM(NVL(CPA.OVERALL_CREDIT_LIMIT
,-1) * (100 + CP.TOLERANCE) / 100)
,-1),
NVL(SUM(NVL(CPA.TRX_CREDIT_LIMIT
,-1) * (100 + CP.TOLERANCE) / 100)
,-1)
INTO L_TOTAL_LIMIT,L_ORDER_LIMIT
FROM
HZ_CUSTOMER_PROFILES CP,
HZ_CUST_PROFILE_AMTS CPA
WHERE CP.CUST_ACCOUNT_ID = CUSTOMER_ID
AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
AND CPA.CURRENCY_CODE = CURRENCY1
AND CP.SITE_USE_ID IS NULL;
SELECT
INCLUDE_PAYMENTS_AT_RISK_FLAG
INTO L_INCLUDE_RISK_FLAG1
FROM
OE_CREDIT_CHECK_RULES
WHERE CREDIT_CHECK_RULE_ID = ENTRY_RULE_ID;
SELECT
INCLUDE_PAYMENTS_AT_RISK_FLAG
INTO L_INCLUDE_RISK_FLAG2
FROM
OE_CREDIT_CHECK_RULES
WHERE CREDIT_CHECK_RULE_ID = SHIP_RULE_ID;
SELECT
INCLUDE_PAYMENTS_AT_RISK_FLAG
INTO L_INCLUDE_RISK_FLAG3
FROM
OE_CREDIT_CHECK_RULES
WHERE CREDIT_CHECK_RULE_ID = PICK_RULE_ID;
SELECT
INCLUDE_PAYMENTS_AT_RISK_FLAG
INTO L_INCLUDE_RISK_FLAG4
FROM
OE_CREDIT_CHECK_RULES
WHERE CREDIT_CHECK_RULE_ID = PACK_RULE_ID;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_CREDIT
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y';
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_CREDIT
INTO L_REC_BAL_CREDIT
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_CREDIT
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND SYSDATE - TRX_DATE > ENTRY_OPEN_AR_DAYS;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_CREDIT
INTO L_REC_BAL_CREDIT
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND SYSDATE - CR.RECEIPT_DATE > ENTRY_OPEN_AR_DAYS;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_CREDIT
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND INVOICE_CURRENCY_CODE = CURRENCY1;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_CREDIT
INTO L_REC_BAL_CREDIT
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_CREDIT
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND SYSDATE - TRX_DATE > ENTRY_OPEN_AR_DAYS;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_CREDIT
INTO L_REC_BAL_CREDIT
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND SYSDATE - CR.RECEIPT_DATE > ENTRY_OPEN_AR_DAYS;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_SHIP
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y';
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_SHIP
INTO L_REC_BAL_SHIP
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_SHIP
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND SYSDATE - TRX_DATE > SHIP_OPEN_AR_DAYS;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_SHIP
INTO L_REC_BAL_SHIP
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND SYSDATE - CR.RECEIPT_DATE > SHIP_OPEN_AR_DAYS;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_SHIP
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND INVOICE_CURRENCY_CODE = CURRENCY1;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_SHIP
INTO L_REC_BAL_SHIP
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_SHIP
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND SYSDATE - TRX_DATE > SHIP_OPEN_AR_DAYS;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_SHIP
INTO L_REC_BAL_SHIP
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND SYSDATE - CR.RECEIPT_DATE > SHIP_OPEN_AR_DAYS;
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_CREDIT
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.BOOKED_FLAG = 'Y'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND DECODE(ENTRY_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_CREDIT
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(ENTRY_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_CREDIT
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.BOOKED_FLAG = 'Y'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND DECODE(ENTRY_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - -NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_CREDIT
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(ENTRY_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_CREDIT
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
AND SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND L.BOOKED_FLAG = 'Y'
AND DECODE(ENTRY_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - -NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_CREDIT
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(ENTRY_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_CREDIT
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND L.BOOKED_FLAG = 'Y'
AND DECODE(ENTRY_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - -NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_CREDIT
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(ENTRY_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_SHIP
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.BOOKED_FLAG = 'Y'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND DECODE(SHIP_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_SHIP
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(SHIP_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_SHIP
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.BOOKED_FLAG = 'Y'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND DECODE(SHIP_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_SHIP
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(SHIP_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_SHIP
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
AND SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND L.BOOKED_FLAG = 'Y'
AND DECODE(SHIP_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_SHIP
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(SHIP_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_SHIP
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND L.BOOKED_FLAG = 'Y'
AND DECODE(SHIP_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_SHIP
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(SHIP_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_PICK
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y';
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_PICK
INTO L_REC_BAL_PICK
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_PICK
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND SYSDATE - TRX_DATE > PICK_OPEN_AR_DAYS;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_PICK
INTO L_REC_BAL_PICK
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND SYSDATE - CR.RECEIPT_DATE > PICK_OPEN_AR_DAYS;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_PICK
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND INVOICE_CURRENCY_CODE = CURRENCY1;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_PICK
INTO L_REC_BAL_PICK
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_PICK
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND SYSDATE - TRX_DATE > PICK_OPEN_AR_DAYS;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_PICK
INTO L_REC_BAL_PICK
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND SYSDATE - CR.RECEIPT_DATE > PICK_OPEN_AR_DAYS;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_PACK
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y';
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_PACK
INTO L_REC_BAL_PACK
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_PACK
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND SYSDATE - TRX_DATE > PACK_OPEN_AR_DAYS;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_PACK
INTO L_REC_BAL_PACK
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND SYSDATE - CR.RECEIPT_DATE > PACK_OPEN_AR_DAYS;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_PACK
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND INVOICE_CURRENCY_CODE = CURRENCY1;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_PACK
INTO L_REC_BAL_PACK
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID;
SELECT
NVL(SUM(AMOUNT_DUE_REMAINING)
,0)
INTO L_REC_BAL_PACK
FROM
AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND NVL(RECEIPT_CONFIRMED_FLAG
,'Y') = 'Y'
AND INVOICE_CURRENCY_CODE = CURRENCY1
AND SYSDATE - TRX_DATE > PACK_OPEN_AR_DAYS;
SELECT
NVL(SUM(CRH.AMOUNT)
,0) + L_REC_BAL_PACK
INTO L_REC_BAL_PACK
FROM
AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG
,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
,'Y'
,'RISK_ELIMINATED'
,'CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = CURRENCY1
AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND SYSDATE - CR.RECEIPT_DATE > PACK_OPEN_AR_DAYS;
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_PICK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.BOOKED_FLAG = 'Y'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND DECODE(PICK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_PICK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PICK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_PICK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.BOOKED_FLAG = 'Y'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND DECODE(PICK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_PICK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PICK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_PICK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
AND SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PICK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_PICK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PICK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_PICK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PICK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_PICK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PICK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_PACK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.BOOKED_FLAG = 'Y'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND DECODE(PACK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_PACK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PACK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_PACK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.BOOKED_FLAG = 'Y'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND DECODE(PACK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_PACK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PACK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_PACK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
AND SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PACK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_PACK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PACK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * NVL(L.ORDERED_QUANTITY
,0))
,0)
INTO L_TOTAL1_PACK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
,'X') not in ( 'PARTIAL' , 'YES' )
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PACK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );
SELECT
NVL(SUM(NVL(L.UNIT_SELLING_PRICE
,0) * (NVL(L.ORDERED_QUANTITY
,0) - NVL(L.SHIPPED_QUANTITY
,0)))
,0)
INTO L_TOTAL3_PACK
FROM
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS H,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
,-99) = NVL(LOC_ASSIGN.ORG_ID
,-99)
AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
AND L.BOOKED_FLAG = 'Y'
AND DECODE(PACK_SHIPPING_INTERVAL
,-1
,TRUNC(SYSDATE)
,NVL(L.REQUEST_DATE
,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
AND not exists (
SELECT
'x'
FROM
OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID is null );