AP_COSTING_TRANSFER_EXTRACT

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
INVOICE_ID INVOICING_BUSINESS_UNIT_ID INVOICE_AMT INVOICE_QTY UNIT_OF_MEASURE DISTRIBUTION_MATCH_TYPE INVOICE_DISTRIBUTION_TYPE PO_DISTRIBUTION_ID RCV_TRANSACTION_ID FINAL_MATCH_FLAG ACCOUNTING_DATE RELATED_DISTRIBUTION_ID CORRECTED_INVOICE_DIST_ID REVERSAL_FLAG PARENT_REVERSAL_ID INVENTORY_TRANSFER_STATUS EXTERNAL_SYSTEM_REF_ID INVOICE_LINE_NUMBER MATCH_STATUS_FLAG INVOICE_SOURCE INVOICE_TYPE CURRENCY_CODE INVOICE_NUMBER CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_TYPE INVOICE_CREATION_DATE EXPENDITURE_ITEM_DATE DIST_CODE_COMBINATION_ID INVOICE_BASE_AMOUNT LCM_ENABLED_FLAG VENDOR_ID VENDOR_SITE_ID TAX_CODE_ID LINE_CANCELLED_FLAG INVOICE_LINE_TYPE CHARGE_APPLICABLE_TO_DIST_ID CONSUMPTION_ADVICE_HEADER_ID CONSUMPTION_ADVICE_LINE_ID FISCAL_DOC_HEADER_ID FISCAL_DOC_LINE_ID FISCAL_CHARGE_TYPE EXTERNAL_SYSTEM_REFERENCE SELF_ASSESSED_FLAG INCLUSIVE_FLAG TAX_POINT_BASIS

Name

INVOICE_ID

INVOICING_BUSINESS_UNIT_ID

INVOICE_AMT

INVOICE_QTY

UNIT_OF_MEASURE

DISTRIBUTION_MATCH_TYPE

INVOICE_DISTRIBUTION_TYPE

PO_DISTRIBUTION_ID

RCV_TRANSACTION_ID

FINAL_MATCH_FLAG

ACCOUNTING_DATE

RELATED_DISTRIBUTION_ID

CORRECTED_INVOICE_DIST_ID

REVERSAL_FLAG

PARENT_REVERSAL_ID

INVENTORY_TRANSFER_STATUS

EXTERNAL_SYSTEM_REF_ID

INVOICE_LINE_NUMBER

MATCH_STATUS_FLAG

INVOICE_SOURCE

INVOICE_TYPE

CURRENCY_CODE

INVOICE_NUMBER

CURRENCY_CONVERSION_DATE

CURRENCY_CONVERSION_RATE

CURRENCY_CONVERSION_TYPE

INVOICE_CREATION_DATE

EXPENDITURE_ITEM_DATE

DIST_CODE_COMBINATION_ID

INVOICE_BASE_AMOUNT

LCM_ENABLED_FLAG

VENDOR_ID

VENDOR_SITE_ID

TAX_CODE_ID

LINE_CANCELLED_FLAG

INVOICE_LINE_TYPE

CHARGE_APPLICABLE_TO_DIST_ID

CONSUMPTION_ADVICE_HEADER_ID

CONSUMPTION_ADVICE_LINE_ID

FISCAL_DOC_HEADER_ID

FISCAL_DOC_LINE_ID

FISCAL_CHARGE_TYPE

EXTERNAL_SYSTEM_REFERENCE

SELF_ASSESSED_FLAG

INCLUSIVE_FLAG

TAX_POINT_BASIS

Query

SQL_Statement
SELECT aid.invoice_id, aid.org_id AS INVOICING_BUSINESS_UNIT_ID, aid.amount AS INVOICE_AMT, DECODE(aid.corrected_invoice_dist_id,NULL,aid.quantity_invoiced,DECODE(aid.dist_match_type,'QTY_CORRECTION',aid.corrected_quantity,NULL)) AS INVOICE_QTY, aid.matched_uom_lookup_code AS UNIT_OF_MEASURE, aid.dist_match_type AS DISTRIBUTION_MATCH_TYPE, aid.line_type_lookup_code AS INVOICE_DISTRIBUTION_TYPE, decode(aid.line_type_lookup_code,'REC_TAX',aid1.po_distribution_id,aid.po_distribution_id) po_distribution_id, decode(aid.line_type_lookup_code,'REC_TAX',aid1.rcv_transaction_id,aid.rcv_transaction_id) rcv_transaction_id, aid.final_match_flag, aid.accounting_date, aid.related_id AS RELATED_DISTRIBUTION_ID, aid.corrected_invoice_dist_id, aid.reversal_flag, aid.parent_reversal_id, aid.inventory_transfer_status, aid.invoice_distribution_id AS EXTERNAL_SYSTEM_REF_ID, aid.invoice_line_number, aid.match_status_flag, ai.source AS INVOICE_SOURCE, ai.invoice_type_lookup_code AS INVOICE_TYPE, ai.invoice_currency_code AS CURRENCY_CODE, ai.invoice_num AS INVOICE_NUMBER, ai.exchange_date AS CURRENCY_CONVERSION_DATE, ai.exchange_rate AS CURRENCY_CONVERSION_RATE, ai.exchange_rate_type AS CURRENCY_CONVERSION_TYPE, aid.CREATION_DATE AS INVOICE_CREATION_DATE, aid.pjc_expenditure_item_date AS EXPENDITURE_ITEM_DATE, aid.dist_code_combination_id, nvl(aid.base_amount,aid.amount) AS INVOICE_BASE_AMOUNT, ail.lcm_enabled_flag AS LCM_ENABLED_FLAG, ai.vendor_id AS VENDOR_ID, ai.vendor_site_id AS VENDOR_SITE_ID, aid.tax_code_id, DECODE(ail.discarded_flag, 'Y', 'Y', ail.cancelled_flag) AS LINE_CANCELLED_FLAG, ail.line_type_lookup_code AS INVOICE_LINE_TYPE, aid.charge_applicable_to_dist_id, aid.consumption_advice_header_id, aid.consumption_advice_line_id, ail.source_trx_id AS FISCAL_DOC_HEADER_ID, ail.source_line_id AS FISCAL_DOC_LINE_ID, ail.fiscal_charge_type AS FISCAL_CHARGE_TYPE, 'FUSION' AS EXTERNAL_SYSTEM_REFERENCE, 'N' SELF_ASSESSED_FLAG, NVL((SELECT znrd.inclusive_flag from zx_rec_nrec_dist znrd where znrd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id and application_id = 200 and entity_code = 'AP_INVOICES'),'N') "INCLUSIVE_FLAG", (SELECT znrd.tax_point_basis from zx_rec_nrec_dist znrd where znrd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id and application_id = 200 and entity_code = 'AP_INVOICES') "TAX_POINT_BASIS" FROM AP_INVOICE_DISTRIBUTIONS_ALL aid, AP_INVOICE_DISTRIBUTIONS_ALL aid1, AP_INVOICE_LINES_ALL ail, AP_INVOICES_ALL ai WHERE ai.invoice_id = ail.invoice_id AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id(+) AND NVL(ai.invoice_type_lookup_code,'X') <> 'PREPAYMENT' AND aid.prepay_distribution_id is null AND ail.invoice_id = aid.invoice_id AND ail.line_number = aid.invoice_line_number /* AND nvl(aid.inventory_transfer_status,'N') ='N' / AND aid.inventory_transfer_status ='N' AND aid.posted_flag='Y' AND (aid.po_distribution_id IS NOT NULL OR aid.rcv_transaction_id IS NOT NULL OR ail.lcm_enabled_flag ='Y' OR (AID.LINE_TYPE_LOOKUP_CODE = 'REC_TAX' AND aid1.po_distribution_id IS NOT NULL )) UNION ALL SELECT astd.invoice_id, astd.org_id AS INVOICING_BUSINESS_UNIT_ID, astd.amount AS INVOICE_AMT, DECODE(astd.corrected_invoice_dist_id,NULL,astd.quantity_invoiced,DECODE(astd.dist_match_type,'QTY_CORRECTION',astd.corrected_quantity,NULL)) AS INVOICE_QTY, astd.matched_uom_lookup_code AS UNIT_OF_MEASURE, astd.dist_match_type AS DISTRIBUTION_MATCH_TYPE, astd.line_type_lookup_code AS INVOICE_DISTRIBUTION_TYPE, decode(astd.line_type_lookup_code,'REC_TAX',astd1.po_distribution_id,astd.po_distribution_id) po_distribution_id, decode(astd.line_type_lookup_code,'REC_TAX',astd1.rcv_transaction_id,astd.rcv_transaction_id) rcv_transaction_id, astd.final_match_flag, astd.accounting_date, astd.related_id AS RELATED_DISTRIBUTION_ID, astd.corrected_invoice_dist_id, astd.reversal_flag, astd.parent_reversal_id, astd.inventory_transfer_status, astd.invoice_distribution_id AS EXTERNAL_SYSTEM_REF_ID, astd.invoice_line_number, astd.match_status_flag, ai.source AS INVOICE_SOURCE, ai.invoice_type_lookup_code AS INVOICE_TYPE, ai.invoice_currency_code AS CURRENCY_CODE, ai.invoice_num AS INVOICE_NUMBER, ai.exchange_date AS CURRENCY_CONVERSION_DATE, ai.exchange_rate AS CURRENCY_CONVERSION_RATE, ai.exchange_rate_type AS CURRENCY_CONVERSION_TYPE, astd.creation_date AS INVOICE_CREATION_DATE, astd.pjc_expenditure_item_date AS EXPENDITURE_ITEM_DATE, astd.dist_code_combination_id, nvl(astd.base_amount,astd.AMOUNT) AS INVOICE_BASE_AMOUNT, ail.lcm_enabled_flag AS LCM_ENABLED_FLAG, ai.vendor_id AS VENDOR_ID, ai.vendor_site_id AS VENDOR_SITE_ID, astd.tax_code_id, DECODE(ail.discarded_flag, 'Y', 'Y', ail.cancelled_flag) AS LINE_CANCELLED_FLAG, ail.line_type_lookup_code AS INVOICE_LINE_TYPE, astd.charge_applicable_to_dist_id, NULL CONSUMPTION_ADVICE_HEADER_ID, NULL CONSUMPTION_ADVICE_LINE_ID, ail.source_trx_id AS FISCAL_DOC_HEADER_ID, ail.source_line_id AS FISCAL_DOC_LINE_ID, ail.fiscal_charge_type AS FISCAL_CHARGE_TYPE, 'FUSION' AS EXTERNAL_SYSTEM_REFERENCE, 'Y' SELF_ASSESSED_FLAG, 'N' "INCLUSIVE_FLAG", (SELECT znrd.tax_point_basis from zx_rec_nrec_dist znrd where znrd.rec_nrec_tax_dist_id = astd.detail_tax_dist_id and application_id = 200 and entity_code ='AP_INVOICES') "TAX_POINT_BASIS" FROM AP_SELF_ASSESSED_TAX_DIST_ALL astd, AP_INVOICE_LINES_ALL ail, AP_INVOICES_ALL ai, AP_SELF_ASSESSED_TAX_DIST_ALL astd1 WHERE ai.invoice_id = ail.invoice_id AND astd.charge_applicable_to_dist_id = astd1.invoice_distribution_id(+) AND NVL(ai.invoice_type_lookup_code,'X') <> 'PREPAYMENT' AND astd.prepay_distribution_id is null AND ail.invoice_id = astd.invoice_id AND ail.line_number = astd.invoice_line_number / AND nvl(astd.inventory_transfer_status,'N') ='N' */ AND astd.inventory_transfer_status ='N' AND astd.posted_flag='Y' AND (astd.po_distribution_id IS NOT NULL OR astd.rcv_transaction_id IS NOT NULL OR ail.lcm_enabled_flag ='Y' OR (astd.LINE_TYPE_LOOKUP_CODE = 'REC_TAX' AND astd1.po_distribution_id IS NOT NULL))

SQL_Statement

SELECT aid.invoice_id,

aid.org_id AS INVOICING_BUSINESS_UNIT_ID,

aid.amount AS INVOICE_AMT, DECODE(aid.corrected_invoice_dist_id,NULL,aid.quantity_invoiced,DECODE(aid.dist_match_type,'QTY_CORRECTION',aid.corrected_quantity,NULL)) AS INVOICE_QTY,

aid.matched_uom_lookup_code AS UNIT_OF_MEASURE,

aid.dist_match_type AS DISTRIBUTION_MATCH_TYPE,

aid.line_type_lookup_code AS INVOICE_DISTRIBUTION_TYPE,

decode(aid.line_type_lookup_code,'REC_TAX',aid1.po_distribution_id,aid.po_distribution_id) po_distribution_id,

decode(aid.line_type_lookup_code,'REC_TAX',aid1.rcv_transaction_id,aid.rcv_transaction_id) rcv_transaction_id,

aid.final_match_flag,

aid.accounting_date,

aid.related_id AS RELATED_DISTRIBUTION_ID,

aid.corrected_invoice_dist_id,

aid.reversal_flag,

aid.parent_reversal_id,

aid.inventory_transfer_status,

aid.invoice_distribution_id AS EXTERNAL_SYSTEM_REF_ID,

aid.invoice_line_number,

aid.match_status_flag,

ai.source AS INVOICE_SOURCE,

ai.invoice_type_lookup_code AS INVOICE_TYPE,

ai.invoice_currency_code AS CURRENCY_CODE,

ai.invoice_num AS INVOICE_NUMBER,

ai.exchange_date AS CURRENCY_CONVERSION_DATE,

ai.exchange_rate AS CURRENCY_CONVERSION_RATE,

ai.exchange_rate_type AS CURRENCY_CONVERSION_TYPE,

aid.CREATION_DATE AS INVOICE_CREATION_DATE,

aid.pjc_expenditure_item_date AS EXPENDITURE_ITEM_DATE,

aid.dist_code_combination_id,

nvl(aid.base_amount,aid.amount) AS INVOICE_BASE_AMOUNT,

ail.lcm_enabled_flag AS LCM_ENABLED_FLAG,

ai.vendor_id AS VENDOR_ID,

ai.vendor_site_id AS VENDOR_SITE_ID,

aid.tax_code_id,

DECODE(ail.discarded_flag, 'Y', 'Y', ail.cancelled_flag) AS LINE_CANCELLED_FLAG,

ail.line_type_lookup_code AS INVOICE_LINE_TYPE,

aid.charge_applicable_to_dist_id,

aid.consumption_advice_header_id,

aid.consumption_advice_line_id,

ail.source_trx_id AS FISCAL_DOC_HEADER_ID,

ail.source_line_id AS FISCAL_DOC_LINE_ID,

ail.fiscal_charge_type AS FISCAL_CHARGE_TYPE,

'FUSION' AS EXTERNAL_SYSTEM_REFERENCE,

'N' SELF_ASSESSED_FLAG,

NVL((SELECT znrd.inclusive_flag from zx_rec_nrec_dist znrd

where znrd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id

and application_id = 200

and entity_code = 'AP_INVOICES'),'N') "INCLUSIVE_FLAG",

(SELECT znrd.tax_point_basis from zx_rec_nrec_dist znrd

where znrd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id

and application_id = 200

and entity_code = 'AP_INVOICES') "TAX_POINT_BASIS"

FROM AP_INVOICE_DISTRIBUTIONS_ALL aid,

AP_INVOICE_DISTRIBUTIONS_ALL aid1,

AP_INVOICE_LINES_ALL ail,

AP_INVOICES_ALL ai

WHERE ai.invoice_id = ail.invoice_id

AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id(+)

AND NVL(ai.invoice_type_lookup_code,'X') <> 'PREPAYMENT'

AND aid.prepay_distribution_id is null

AND ail.invoice_id = aid.invoice_id

AND ail.line_number = aid.invoice_line_number

/* AND nvl(aid.inventory_transfer_status,'N') ='N' */

AND aid.inventory_transfer_status ='N'

AND aid.posted_flag='Y'

AND (aid.po_distribution_id IS NOT NULL

OR aid.rcv_transaction_id IS NOT NULL

OR ail.lcm_enabled_flag ='Y'

OR (AID.LINE_TYPE_LOOKUP_CODE = 'REC_TAX' AND aid1.po_distribution_id IS NOT NULL ))

UNION ALL

SELECT astd.invoice_id,

astd.org_id AS INVOICING_BUSINESS_UNIT_ID,

astd.amount AS INVOICE_AMT, DECODE(astd.corrected_invoice_dist_id,NULL,astd.quantity_invoiced,DECODE(astd.dist_match_type,'QTY_CORRECTION',astd.corrected_quantity,NULL)) AS INVOICE_QTY,

astd.matched_uom_lookup_code AS UNIT_OF_MEASURE,

astd.dist_match_type AS DISTRIBUTION_MATCH_TYPE,

astd.line_type_lookup_code AS INVOICE_DISTRIBUTION_TYPE,

decode(astd.line_type_lookup_code,'REC_TAX',astd1.po_distribution_id,astd.po_distribution_id) po_distribution_id,

decode(astd.line_type_lookup_code,'REC_TAX',astd1.rcv_transaction_id,astd.rcv_transaction_id) rcv_transaction_id,

astd.final_match_flag,

astd.accounting_date,

astd.related_id AS RELATED_DISTRIBUTION_ID,

astd.corrected_invoice_dist_id,

astd.reversal_flag,

astd.parent_reversal_id,

astd.inventory_transfer_status,

astd.invoice_distribution_id AS EXTERNAL_SYSTEM_REF_ID,

astd.invoice_line_number,

astd.match_status_flag,

ai.source AS INVOICE_SOURCE,

ai.invoice_type_lookup_code AS INVOICE_TYPE,

ai.invoice_currency_code AS CURRENCY_CODE,

ai.invoice_num AS INVOICE_NUMBER,

ai.exchange_date AS CURRENCY_CONVERSION_DATE,

ai.exchange_rate AS CURRENCY_CONVERSION_RATE,

ai.exchange_rate_type AS CURRENCY_CONVERSION_TYPE,

astd.creation_date AS INVOICE_CREATION_DATE,

astd.pjc_expenditure_item_date AS EXPENDITURE_ITEM_DATE,

astd.dist_code_combination_id,

nvl(astd.base_amount,astd.AMOUNT) AS INVOICE_BASE_AMOUNT,

ail.lcm_enabled_flag AS LCM_ENABLED_FLAG,

ai.vendor_id AS VENDOR_ID,

ai.vendor_site_id AS VENDOR_SITE_ID,

astd.tax_code_id,

DECODE(ail.discarded_flag, 'Y', 'Y', ail.cancelled_flag) AS LINE_CANCELLED_FLAG,

ail.line_type_lookup_code AS INVOICE_LINE_TYPE,

astd.charge_applicable_to_dist_id,

NULL CONSUMPTION_ADVICE_HEADER_ID,

NULL CONSUMPTION_ADVICE_LINE_ID,

ail.source_trx_id AS FISCAL_DOC_HEADER_ID,

ail.source_line_id AS FISCAL_DOC_LINE_ID,

ail.fiscal_charge_type AS FISCAL_CHARGE_TYPE,

'FUSION' AS EXTERNAL_SYSTEM_REFERENCE,

'Y' SELF_ASSESSED_FLAG,

'N' "INCLUSIVE_FLAG",

(SELECT znrd.tax_point_basis from zx_rec_nrec_dist znrd

where znrd.rec_nrec_tax_dist_id = astd.detail_tax_dist_id

and application_id = 200

and entity_code ='AP_INVOICES') "TAX_POINT_BASIS"

FROM AP_SELF_ASSESSED_TAX_DIST_ALL astd,

AP_INVOICE_LINES_ALL ail,

AP_INVOICES_ALL ai,

AP_SELF_ASSESSED_TAX_DIST_ALL astd1

WHERE ai.invoice_id = ail.invoice_id

AND astd.charge_applicable_to_dist_id = astd1.invoice_distribution_id(+)

AND NVL(ai.invoice_type_lookup_code,'X') <> 'PREPAYMENT'

AND astd.prepay_distribution_id is null

AND ail.invoice_id = astd.invoice_id

AND ail.line_number = astd.invoice_line_number

/* AND nvl(astd.inventory_transfer_status,'N') ='N' */

AND astd.inventory_transfer_status ='N'

AND astd.posted_flag='Y'

AND (astd.po_distribution_id IS NOT NULL

OR astd.rcv_transaction_id IS NOT NULL

OR ail.lcm_enabled_flag ='Y'

OR (astd.LINE_TYPE_LOOKUP_CODE = 'REC_TAX' AND astd1.po_distribution_id IS NOT NULL))