WIP Offsetting Account

Description
Categories: BI Publisher, Manufacturing
Application: Work in Process
Source: WIP Offsetting Account Report (XML)
Short Name: WIPUTOAC_XML
DB package: WIP_WIPUTOAC_XMLP_PKG
SELECT null              C_M_ACCT_FLEX
,      null              C_D_ACCT_FLEX
,      WTA2.TRANSACTION_DATE         Txn_Date
,      BR.RESOURCE_CODE            Resource1
,      ML1.MEANING                 Txn_Type
,      WT.TRANSACTION_TYPE         Txn_Type_Number
,      WE.WIP_ENTITY_NAME          Job_Assembly
,      WT.OPERATION_SEQ_NUM        Op_Seq
,      BR.UNIT_OF_MEASURE          UOM
,      round(WTA2.PRIMARY_QUANTITY,:P_qty_precision)       Quantity
,      round(WTA2.RATE_OR_AMOUNT,:C_Curr_Precision)         Rate_or_Amount
,      round(WTA2.BASE_TRANSACTION_VALUE,:C_Curr_Precision) Extended_Cost, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_m_acct_sort', 'SQLGL', 'GL#', GCC1.CHART_OF_ACCOUNTS_ID, NULL, GCC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_M_ACCT_SORT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_m_acct_field', 'SQLGL', 'GL#', GCC1.CHART_OF_ACCOUNTS_ID, NULL, GCC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_M_ACCT_FIELD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_descrip', 'SQLGL', 'GL#', GCC2.CHART_OF_ACCOUNTS_ID, NULL, GCC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_ACCT_DESCRIP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_d_acct_sort', 'SQLGL', 'GL#', GCC2.CHART_OF_ACCOUNTS_ID, NULL, GCC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_D_ACCT_SORT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_d_acct_field', 'SQLGL', 'GL#', GCC2.CHART_OF_ACCOUNTS_ID, NULL, GCC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_D_ACCT_FIELD
FROM   WIP_ENTITIES WE
,      MFG_LOOKUPS ML1
,      BOM_RESOURCES BR
,      GL_CODE_COMBINATIONS GCC1
,      GL_CODE_COMBINATIONS GCC2
,      WIP_TRANSACTION_ACCOUNTS WTA1
,      WIP_TRANSACTION_ACCOUNTS WTA2
,      WIP_TRANSACTIONS WT
WHERE    WT.ORGANIZATION_ID = :P_ORGANIZATION_ID
&C_Limit_Dates 
AND    WT.TRANSACTION_ID = WTA2.TRANSACTION_ID
AND    WTA2.TRANSACTION_ID = WTA1.TRANSACTION_ID
AND    WTA2.CONTRA_SET_ID = WTA1.CONTRA_SET_ID
AND    WTA2.REFERENCE_ACCOUNT <> WTA1.REFERENCE_ACCOUNT
AND    WTA2.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WTA1.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WTA2.ACCOUNTING_LINE_TYPE <> 15
AND    WTA1.ACCOUNTING_LINE_TYPE <> 15
AND    GCC1.CODE_COMBINATION_ID = WTA1.REFERENCE_ACCOUNT
--&C_Limit_Accounts 
and &P_ACCT_WHERE
AND    GCC2.CODE_COMBINATION_ID = WTA2.REFERENCE_ACCOUNT
AND    ML1.LOOKUP_TYPE='WIP_TRANSACTION_TYPE_SHORT'
AND    ML1.LOOKUP_CODE=WT.TRANSACTION_TYPE
AND    WE.WIP_ENTITY_ID = WTA2.WIP_ENTITY_ID
AND    WE.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    BR.RESOURCE_ID(+) = WTA2.RESOURCE_ID
AND    BR.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
GROUP BY  null
,        null
,        WTA2.TRANSACTION_DATE
,        ML1.MEANING
,        WT.TRANSACTION_TYPE  
,        WE.WIP_ENTITY_NAME
,        WT.OPERATION_SEQ_NUM
,        BR.RESOURCE_CODE
,        BR.UNIT_OF_MEASURE 
,        WT.CREATION_DATE
,        WTA2.TRANSACTION_ID
,        round(WTA2.PRIMARY_QUANTITY,:P_qty_precision)      
,        round(WTA2.RATE_OR_AMOUNT,:C_Curr_Precision)        
,        round(WTA2.BASE_TRANSACTION_VALUE,:C_Curr_Precision)
,        WTA2.ROWID
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_m_acct_sort', 'SQLGL', 'GL#', GCC1.CHART_OF_ACCOUNTS_ID, NULL, GCC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_m_acct_field', 'SQLGL', 'GL#', GCC1.CHART_OF_ACCOUNTS_ID, NULL, GCC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_descrip', 'SQLGL', 'GL#', GCC2.CHART_OF_ACCOUNTS_ID, NULL, GCC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_d_acct_sort', 'SQLGL', 'GL#', GCC2.CHART_OF_ACCOUNTS_ID, NULL, GCC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_d_acct_field', 'SQLGL', 'GL#', GCC2.CHART_OF_ACCOUNTS_ID, NULL, GCC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')
order by C_M_ACCT_SORT,C_D_ACCT_SORT
Parameter Name SQL text Validation
Quantity Precision
 
Number
Org_id
 
Number
Flex Structure Number
 
Number
To
 
Accounts From
 
To
 
Date
Dates From
 
Date