select trunc(pawo.write_off_gl_date) Write_off_date
, null C_FLEX_ITEM
, null C_FLEX_ACC
, pawo.po_num PO_Number
, pawo.po_line_num PO_line_num
, pawo.transaction_source_code Source_code
, plc.displayed_field Source
, pawo.transaction_organization_id Organization_id
, ood.organization_code Organization
, to_char(pawo.transaction_date,'DD-MON-YY') Document_date
, nvl(pawo.invoice_num, pawo.receipt_num) Document_number
, pawo.invoice_line_num Document_line
, round(pawo.transaction_quantity,:P_QTY_PRECISION) Quantity
, pawo.transaction_unit_price Unit_price
, pawo.transaction_amount C_AMOUNT_Amount
, plc1.displayed_field Destination
, pawo.accrual_account_id Account
, mtr.reason_name Reason_code
, pawo.comments Comments
, decode(pawo.write_off_code,
'REVERSE WRITE OFF', 'Yes',
'WRITE OFF', 'No') write_off_reversal,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_disp', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ACC_DISP,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_sort', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_FLEX_ACC_SORT,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP
from cst_pac_accrual_write_offs pawo
, mtl_transaction_reasons mtr
, mtl_system_items msi
, gl_code_combinations gcc
, po_lines_all pol
, org_organization_definitions ood
, po_lookup_codes plc
, po_lookup_codes plc1
where pawo.legal_entity_id = :P_LEGAL_ENTITY
and pawo.cost_type_id = :P_COST_TYPE
and pawo.cost_group_id = :P_COST_GROUP
and pawo.reason_id = mtr.reason_id (+)
and msi.inventory_item_id (+) = pol.item_id
and msi.organization_id = pawo.transaction_organization_id
and gcc.code_combination_id = pawo.accrual_account_id
and pol.po_line_id (+) = pawo.po_line_id
and pawo.period_id = :P_PERIOD_ID
and nvl(mtr.reason_name,'A') = nvl(:P_reason_code, nvl(mtr.reason_name,'A'))
and ood.legal_entity = :P_LEGAL_ENTITY
and ood.organization_id = pawo.transaction_organization_id
and plc.lookup_code = pawo.transaction_source_code
and plc.lookup_type = 'ACCRUAL TRX SOURCE'
and plc1.lookup_code(+) = pawo.destination_type_code
and plc1.lookup_type(+) = 'DESTINATION TYPE'
ORDER BY fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_disp', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')
, plc1.displayed_field
, :P_ORDERBY_ITEM
, pawo.po_num
, pawo.po_line_num
, decode(plc.displayed_field, 'PO', 1,
'INV', 2, 'WIP', 3, 'AP', 4, 5)
, pawo.transaction_date |