VIEW: APPS.PA_CUST_RDL_ALL_MRC_V

Source

View Text - Preformatted

SELECT mc.EXPENDITURE_ITEM_ID , mc.LINE_NUM , mc.AMOUNT , mc.BILL_AMOUNT , mc.REQUEST_ID , mc.PROGRAM_APPLICATION_ID , mc.PROGRAM_ID , mc.PROGRAM_UPDATE_DATE , mc.BATCH_NAME , mc.RAW_COST , mc.PROJECT_ID, mc.DRAFT_REVENUE_NUM, mc.DRAFT_REVENUE_ITEM_LINE_NUM, mc.DRAFT_INVOICE_NUM, mc.DRAFT_INVOICE_ITEM_LINE_NUM, pa.CREATION_DATE, pa.CREATED_BY, pa.CODE_COMBINATION_ID, pa.FUNCTION_CODE, pa.FUNCTION_TRANSACTION_CODE, pa.ADDITIONAL_REVENUE_FLAG, pa.INVOICE_ELIGIBLE_FLAG, pa.REVERSED_FLAG, pa.LINE_NUM_REVERSED, pa.REV_IND_COMPILED_SET_ID, pa.INV_IND_COMPILED_SET_ID, pa.OUTPUT_VAT_TAX_ID, pa.OUTPUT_TAX_CLASSIFICATION_CODE, pa.OUTPUT_TAX_EXEMPT_FLAG, pa.OUTPUT_TAX_EXEMPT_REASON_CODE, pa.OUTPUT_TAX_EXEMPT_NUMBER, pa.PRC_GENERATED_FLAG, pa.ORG_ID, mc.currency_code, mc.amount, mc.bill_amount, mc.exchange_rate, mc.conversion_date, mc.rate_type, pa.bill_trans_currency_code, pa.bill_trans_amount, pa.bill_trans_bill_amount, pa.bill_rate, pa.revproc_currency_code, pa.revproc_rate_type, pa.revproc_rate_date, pa.revproc_exchange_rate, pa.invproc_currency_code, pa.invproc_rate_type, pa.invproc_rate_date, pa.invproc_exchange_rate, pa.project_currency_code, pa.project_rev_rate_type, pa.project_rev_rate_date, pa.project_rev_exchange_rate, pa.project_revenue_amount, pa.project_inv_rate_type, pa.project_inv_rate_date, pa.project_inv_exchange_rate, pa.project_bill_amount, mc.PROJFUNC_INV_RATE_TYPE, mc.PROJFUNC_INV_RATE_DATE, mc.PROJFUNC_INV_EXCHANGE_RATE, pa.funding_currency_code, pa.funding_rev_rate_type, pa.funding_rev_rate_date, pa.funding_rev_exchange_rate, pa.funding_revenue_amount, pa.funding_inv_rate_type, pa.funding_inv_rate_date, pa.funding_inv_exchange_rate, pa.funding_bill_amount, pa.LABOR_MULTIPLIER, pa.DISCOUNT_PERCENTAGE, pa.AMOUNT_CALCULATION_CODE, pa.BILL_MARKUP_PERCENTAGE, pa.RATE_SOURCE_ID, pa.INV_GEN_REJECTION_CODE, pa.retention_rule_id, pa.retn_draft_invoice_num, pa.retn_draft_invoice_line_num, pa.retained_amount, pa.rate_disc_reason_code , NVL(pa.REVTRANS_AMOUNT, pa.AMOUNT) , NVL(pa.REVTRANS_CURRENCY_CODE, pa.REVPROC_CURRENCY_CODE) , NVL(pa.REVPROC_REVTRANS_RATE_TYPE, pa.REVPROC_RATE_TYPE) , NVL(pa.REVPROC_REVTRANS_RATE_DATE, pa.REVPROC_RATE_DATE) , NVL(pa.REVPROC_REVTRANS_EX_RATE, pa.REVPROC_EXCHANGE_RATE) FROM PA_CUST_REV_DIST_LINES pa, PA_MC_CUST_RDL_ALL mc WHERE mc.expenditure_item_id = pa.expenditure_item_id AND mc.line_num = pa.line_num AND mc.prc_assignment_id = -99 AND mc.set_of_books_id = NVL( TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO'), 45,10) ), -99) UNION ALL SELECT pa.EXPENDITURE_ITEM_ID , pa.LINE_NUM , pa.AMOUNT , pa.BILL_AMOUNT , pa.REQUEST_ID , pa.PROGRAM_APPLICATION_ID , pa.PROGRAM_ID , pa.PROGRAM_UPDATE_DATE , pa.BATCH_NAME , pa.RAW_COST , pa.PROJECT_ID, pa.DRAFT_REVENUE_NUM, pa.DRAFT_REVENUE_ITEM_LINE_NUM, pa.DRAFT_INVOICE_NUM, pa.DRAFT_INVOICE_ITEM_LINE_NUM, pa.CREATION_DATE, pa.CREATED_BY, pa.CODE_COMBINATION_ID, pa.FUNCTION_CODE, pa.FUNCTION_TRANSACTION_CODE, pa.ADDITIONAL_REVENUE_FLAG, pa.INVOICE_ELIGIBLE_FLAG, pa.REVERSED_FLAG, pa.LINE_NUM_REVERSED, pa.REV_IND_COMPILED_SET_ID, pa.INV_IND_COMPILED_SET_ID, pa.OUTPUT_VAT_TAX_ID, pa.OUTPUT_TAX_EXEMPT_FLAG, pa.OUTPUT_TAX_CLASSIFICATION_CODE, pa.OUTPUT_TAX_EXEMPT_REASON_CODE, pa.OUTPUT_TAX_EXEMPT_NUMBER, pa.PRC_GENERATED_FLAG, pa.ORG_ID, pa.projfunc_currency_code, pa.projfunc_revenue_amount, pa.projfunc_bill_amount, pa.projfunc_rev_exchange_rate, pa.projfunc_rev_rate_date, pa.projfunc_rev_rate_type, pa.bill_trans_currency_code, pa.bill_trans_amount, pa.bill_trans_bill_amount, pa.bill_rate, pa.revproc_currency_code, pa.revproc_rate_type, pa.revproc_rate_date, pa.revproc_exchange_rate, pa.invproc_currency_code, pa.invproc_rate_type, pa.invproc_rate_date, pa.invproc_exchange_rate, pa.project_currency_code, pa.project_rev_rate_type, pa.project_rev_rate_date, pa.project_rev_exchange_rate, pa.project_revenue_amount, pa.project_inv_rate_type, pa.project_inv_rate_date, pa.project_inv_exchange_rate, pa.project_bill_amount, pa.PROJFUNC_INV_RATE_TYPE, pa.PROJFUNC_INV_RATE_DATE, pa.PROJFUNC_INV_EXCHANGE_RATE, pa.funding_currency_code, pa.funding_rev_rate_type, pa.funding_rev_rate_date, pa.funding_rev_exchange_rate, pa.funding_revenue_amount, pa.funding_inv_rate_type, pa.funding_inv_rate_date, pa.funding_inv_exchange_rate, pa.funding_bill_amount, pa.LABOR_MULTIPLIER, pa.DISCOUNT_PERCENTAGE, pa.AMOUNT_CALCULATION_CODE, pa.BILL_MARKUP_PERCENTAGE, pa.RATE_SOURCE_ID, pa.INV_GEN_REJECTION_CODE, pa.retention_rule_id, pa.retn_draft_invoice_num, pa.retn_draft_invoice_line_num, pa.retained_amount, pa.rate_disc_reason_code , NVL(pa.REVTRANS_AMOUNT, pa.AMOUNT) , NVL(pa.REVTRANS_CURRENCY_CODE, pa.REVPROC_CURRENCY_CODE) , NVL(pa.REVPROC_REVTRANS_RATE_TYPE, pa.REVPROC_RATE_TYPE) , NVL(pa.REVPROC_REVTRANS_RATE_DATE, pa.REVPROC_RATE_DATE) , NVL(pa.REVPROC_REVTRANS_EX_RATE, pa.REVPROC_EXCHANGE_RATE) FROM PA_CUST_REV_DIST_LINES pa, PA_EXPENDITURE_ITEMS_ALL ei, PA_IMPLEMENTATIONS_ALL imp1, PA_IMPLEMENTATIONS_ALL imp2 WHERE ei.recvr_org_id <> ei.org_id AND ei.expenditure_item_id = pa.expenditure_item_id AND ei.org_id = imp1.org_id AND ei.recvr_org_id = imp2.org_id AND imp1.set_of_books_id <> imp2.set_of_books_id

View Text - HTML Formatted

SELECT MC.EXPENDITURE_ITEM_ID
, MC.LINE_NUM
, MC.AMOUNT
, MC.BILL_AMOUNT
, MC.REQUEST_ID
, MC.PROGRAM_APPLICATION_ID
, MC.PROGRAM_ID
, MC.PROGRAM_UPDATE_DATE
, MC.BATCH_NAME
, MC.RAW_COST
, MC.PROJECT_ID
, MC.DRAFT_REVENUE_NUM
, MC.DRAFT_REVENUE_ITEM_LINE_NUM
, MC.DRAFT_INVOICE_NUM
, MC.DRAFT_INVOICE_ITEM_LINE_NUM
, PA.CREATION_DATE
, PA.CREATED_BY
, PA.CODE_COMBINATION_ID
, PA.FUNCTION_CODE
, PA.FUNCTION_TRANSACTION_CODE
, PA.ADDITIONAL_REVENUE_FLAG
, PA.INVOICE_ELIGIBLE_FLAG
, PA.REVERSED_FLAG
, PA.LINE_NUM_REVERSED
, PA.REV_IND_COMPILED_SET_ID
, PA.INV_IND_COMPILED_SET_ID
, PA.OUTPUT_VAT_TAX_ID
, PA.OUTPUT_TAX_CLASSIFICATION_CODE
, PA.OUTPUT_TAX_EXEMPT_FLAG
, PA.OUTPUT_TAX_EXEMPT_REASON_CODE
, PA.OUTPUT_TAX_EXEMPT_NUMBER
, PA.PRC_GENERATED_FLAG
, PA.ORG_ID
, MC.CURRENCY_CODE
, MC.AMOUNT
, MC.BILL_AMOUNT
, MC.EXCHANGE_RATE
, MC.CONVERSION_DATE
, MC.RATE_TYPE
, PA.BILL_TRANS_CURRENCY_CODE
, PA.BILL_TRANS_AMOUNT
, PA.BILL_TRANS_BILL_AMOUNT
, PA.BILL_RATE
, PA.REVPROC_CURRENCY_CODE
, PA.REVPROC_RATE_TYPE
, PA.REVPROC_RATE_DATE
, PA.REVPROC_EXCHANGE_RATE
, PA.INVPROC_CURRENCY_CODE
, PA.INVPROC_RATE_TYPE
, PA.INVPROC_RATE_DATE
, PA.INVPROC_EXCHANGE_RATE
, PA.PROJECT_CURRENCY_CODE
, PA.PROJECT_REV_RATE_TYPE
, PA.PROJECT_REV_RATE_DATE
, PA.PROJECT_REV_EXCHANGE_RATE
, PA.PROJECT_REVENUE_AMOUNT
, PA.PROJECT_INV_RATE_TYPE
, PA.PROJECT_INV_RATE_DATE
, PA.PROJECT_INV_EXCHANGE_RATE
, PA.PROJECT_BILL_AMOUNT
, MC.PROJFUNC_INV_RATE_TYPE
, MC.PROJFUNC_INV_RATE_DATE
, MC.PROJFUNC_INV_EXCHANGE_RATE
, PA.FUNDING_CURRENCY_CODE
, PA.FUNDING_REV_RATE_TYPE
, PA.FUNDING_REV_RATE_DATE
, PA.FUNDING_REV_EXCHANGE_RATE
, PA.FUNDING_REVENUE_AMOUNT
, PA.FUNDING_INV_RATE_TYPE
, PA.FUNDING_INV_RATE_DATE
, PA.FUNDING_INV_EXCHANGE_RATE
, PA.FUNDING_BILL_AMOUNT
, PA.LABOR_MULTIPLIER
, PA.DISCOUNT_PERCENTAGE
, PA.AMOUNT_CALCULATION_CODE
, PA.BILL_MARKUP_PERCENTAGE
, PA.RATE_SOURCE_ID
, PA.INV_GEN_REJECTION_CODE
, PA.RETENTION_RULE_ID
, PA.RETN_DRAFT_INVOICE_NUM
, PA.RETN_DRAFT_INVOICE_LINE_NUM
, PA.RETAINED_AMOUNT
, PA.RATE_DISC_REASON_CODE
, NVL(PA.REVTRANS_AMOUNT
, PA.AMOUNT)
, NVL(PA.REVTRANS_CURRENCY_CODE
, PA.REVPROC_CURRENCY_CODE)
, NVL(PA.REVPROC_REVTRANS_RATE_TYPE
, PA.REVPROC_RATE_TYPE)
, NVL(PA.REVPROC_REVTRANS_RATE_DATE
, PA.REVPROC_RATE_DATE)
, NVL(PA.REVPROC_REVTRANS_EX_RATE
, PA.REVPROC_EXCHANGE_RATE)
FROM PA_CUST_REV_DIST_LINES PA
, PA_MC_CUST_RDL_ALL MC
WHERE MC.EXPENDITURE_ITEM_ID = PA.EXPENDITURE_ITEM_ID
AND MC.LINE_NUM = PA.LINE_NUM
AND MC.PRC_ASSIGNMENT_ID = -99
AND MC.SET_OF_BOOKS_ID = NVL( TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO')
, 45
, 10) )
, -99) UNION ALL SELECT PA.EXPENDITURE_ITEM_ID
, PA.LINE_NUM
, PA.AMOUNT
, PA.BILL_AMOUNT
, PA.REQUEST_ID
, PA.PROGRAM_APPLICATION_ID
, PA.PROGRAM_ID
, PA.PROGRAM_UPDATE_DATE
, PA.BATCH_NAME
, PA.RAW_COST
, PA.PROJECT_ID
, PA.DRAFT_REVENUE_NUM
, PA.DRAFT_REVENUE_ITEM_LINE_NUM
, PA.DRAFT_INVOICE_NUM
, PA.DRAFT_INVOICE_ITEM_LINE_NUM
, PA.CREATION_DATE
, PA.CREATED_BY
, PA.CODE_COMBINATION_ID
, PA.FUNCTION_CODE
, PA.FUNCTION_TRANSACTION_CODE
, PA.ADDITIONAL_REVENUE_FLAG
, PA.INVOICE_ELIGIBLE_FLAG
, PA.REVERSED_FLAG
, PA.LINE_NUM_REVERSED
, PA.REV_IND_COMPILED_SET_ID
, PA.INV_IND_COMPILED_SET_ID
, PA.OUTPUT_VAT_TAX_ID
, PA.OUTPUT_TAX_EXEMPT_FLAG
, PA.OUTPUT_TAX_CLASSIFICATION_CODE
, PA.OUTPUT_TAX_EXEMPT_REASON_CODE
, PA.OUTPUT_TAX_EXEMPT_NUMBER
, PA.PRC_GENERATED_FLAG
, PA.ORG_ID
, PA.PROJFUNC_CURRENCY_CODE
, PA.PROJFUNC_REVENUE_AMOUNT
, PA.PROJFUNC_BILL_AMOUNT
, PA.PROJFUNC_REV_EXCHANGE_RATE
, PA.PROJFUNC_REV_RATE_DATE
, PA.PROJFUNC_REV_RATE_TYPE
, PA.BILL_TRANS_CURRENCY_CODE
, PA.BILL_TRANS_AMOUNT
, PA.BILL_TRANS_BILL_AMOUNT
, PA.BILL_RATE
, PA.REVPROC_CURRENCY_CODE
, PA.REVPROC_RATE_TYPE
, PA.REVPROC_RATE_DATE
, PA.REVPROC_EXCHANGE_RATE
, PA.INVPROC_CURRENCY_CODE
, PA.INVPROC_RATE_TYPE
, PA.INVPROC_RATE_DATE
, PA.INVPROC_EXCHANGE_RATE
, PA.PROJECT_CURRENCY_CODE
, PA.PROJECT_REV_RATE_TYPE
, PA.PROJECT_REV_RATE_DATE
, PA.PROJECT_REV_EXCHANGE_RATE
, PA.PROJECT_REVENUE_AMOUNT
, PA.PROJECT_INV_RATE_TYPE
, PA.PROJECT_INV_RATE_DATE
, PA.PROJECT_INV_EXCHANGE_RATE
, PA.PROJECT_BILL_AMOUNT
, PA.PROJFUNC_INV_RATE_TYPE
, PA.PROJFUNC_INV_RATE_DATE
, PA.PROJFUNC_INV_EXCHANGE_RATE
, PA.FUNDING_CURRENCY_CODE
, PA.FUNDING_REV_RATE_TYPE
, PA.FUNDING_REV_RATE_DATE
, PA.FUNDING_REV_EXCHANGE_RATE
, PA.FUNDING_REVENUE_AMOUNT
, PA.FUNDING_INV_RATE_TYPE
, PA.FUNDING_INV_RATE_DATE
, PA.FUNDING_INV_EXCHANGE_RATE
, PA.FUNDING_BILL_AMOUNT
, PA.LABOR_MULTIPLIER
, PA.DISCOUNT_PERCENTAGE
, PA.AMOUNT_CALCULATION_CODE
, PA.BILL_MARKUP_PERCENTAGE
, PA.RATE_SOURCE_ID
, PA.INV_GEN_REJECTION_CODE
, PA.RETENTION_RULE_ID
, PA.RETN_DRAFT_INVOICE_NUM
, PA.RETN_DRAFT_INVOICE_LINE_NUM
, PA.RETAINED_AMOUNT
, PA.RATE_DISC_REASON_CODE
, NVL(PA.REVTRANS_AMOUNT
, PA.AMOUNT)
, NVL(PA.REVTRANS_CURRENCY_CODE
, PA.REVPROC_CURRENCY_CODE)
, NVL(PA.REVPROC_REVTRANS_RATE_TYPE
, PA.REVPROC_RATE_TYPE)
, NVL(PA.REVPROC_REVTRANS_RATE_DATE
, PA.REVPROC_RATE_DATE)
, NVL(PA.REVPROC_REVTRANS_EX_RATE
, PA.REVPROC_EXCHANGE_RATE)
FROM PA_CUST_REV_DIST_LINES PA
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_IMPLEMENTATIONS_ALL IMP1
, PA_IMPLEMENTATIONS_ALL IMP2
WHERE EI.RECVR_ORG_ID <> EI.ORG_ID
AND EI.EXPENDITURE_ITEM_ID = PA.EXPENDITURE_ITEM_ID
AND EI.ORG_ID = IMP1.ORG_ID
AND EI.RECVR_ORG_ID = IMP2.ORG_ID
AND IMP1.SET_OF_BOOKS_ID <> IMP2.SET_OF_BOOKS_ID