WIP Account Distribution (1)

Description
Categories: BI Publisher, Manufacturing
Application: Work in Process
Source: WIP Account Distribution Report (XML)
Short Name: WIPUTACD_XML
DB package: WIP_WIPUTACD_XMLP_PKG
select 
&P_FLEXDATA C_FLEXDATA,
       L.description Description,
       WE.wip_entity_name Job_Schedule,
       WL.line_code Line,
      null C_ASSY_FLEX,
       MSI.description A_Desc,
       WDJ.class_code D_class,
       WRI.class_code R_class,
	   WFS.class_code F_class,
       WA.transaction_date Txn_date,
       LU.meaning Txn_type,
       WT.operation_seq_num OpSq,
       BD.department_code Department,
       WT.resource_seq_num RsSq,
       BR.resource_code Res_Ovhd,
       BR.unit_of_measure UOM,
       XLU.meaning Basis,
       CA.activity Activity,
       RLU.meaning  StRt,
       round(WA.primary_quantity,:P_qty_precision) Primary_Quantity,
       round(WA.rate_or_amount *:P_EXCHANGE_RATE, :P_Rate_Precision)  Rate_or_Amt,
          round(WA.base_transaction_value * :P_EXCHANGE_RATE, 
                     nvl(:C_Curr_Precision,:P_Currency_Precision))   Extended_cost, 
	WIP_WIPUTACD_XMLP_PKG.c_flex_sortformula(&P_FLEXDATA,fnd_flex_xml_publisher_apis.process_kff_combination_1('account', 'SQLGL', 'GL#', L.CHART_OF_ACCOUNTS_ID, NULL, L.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'), :C_FLEX_SORT) C_FLEX_SORT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('account', 'SQLGL', 'GL#', L.CHART_OF_ACCOUNTS_ID, NULL, L.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') Account, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_descrip', 'SQLGL', 'GL#', L.CHART_OF_ACCOUNTS_ID, NULL, L.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_ACCT_DESCRIP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('assembly', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')  Assembly, 
	WIP_WIPUTACD_XMLP_PKG.class(WRI.class_code, WDJ.class_code, WFS.class_code) Class
from gl_code_combinations L,
     wip_entities WE,
     wip_lines WL,
     mtl_item_categories mic,
     mtl_categories_b mc,
     wip_discrete_jobs WDJ,
     wip_repetitive_items WRI,
	 wip_flow_schedules WFS, --bug#16634601
     mtl_system_items MSI,
     mfg_lookups LU,
     mfg_lookups XLU,
     mfg_lookups RLU,
     cst_activities CA,
     bom_departments BD,
     bom_resources BR,
      wip_transaction_accounts WA,
     wip_transactions WT
where  WT.organization_id = :P_Organization_Id
  &Limit_Dates
and WT.transaction_type = NVL(:P_Trans_Type,WT.transaction_type)
and &P_PROJ_WHERE
and &P_PROJECT_WHERE
and  mic.organization_id(+) = :P_Organization_Id
and  mic.category_set_id(+) = :P_Category_Set
and mic.inventory_item_id(+) = WE.primary_item_id
and  mc.category_id(+) = mic.category_id
and &P_Cat_Where
and NVL(WT.activity_id,-1) = NVL(:P_Activity,NVL(WT.activity_id,-1))
  and NVL(WT.department_id,-1) = NVL(:P_Department,NVL(WT.department_id,-1))
   and NVL(WT.line_id, -1) = NVL(:P_Line,NVL(WT.line_id, -1))
   &P_Limit_Entity
  and WT.transaction_id = WA.transaction_id
  and WA.organization_id = :P_Organization_Id
  &P_Limit_Account
  and NVL(WA.gl_batch_id,-1) = NVL(:P_GL_Batch_Id,NVL(WA.gl_batch_id,-1))
and WA.accounting_line_type <> 15
 and L.chart_of_accounts_id = :P_STRUCT_NUM
  and L.code_combination_id = WA.reference_account
  and WE.organization_id = :P_Organization_Id
  and WE.wip_entity_id = WA.wip_entity_id
   and NVL(WE.primary_item_id,-1) = NVL(:P_Assembly_Id,NVL(WE.primary_item_id,-1))
  and WDJ.organization_id (+)  = :P_Organization_Id
  and WDJ.wip_entity_id (+) = WA.wip_entity_id
  and WFS.organization_id (+)  = :P_Organization_Id  
  and WFS.wip_entity_id (+) = WA.wip_entity_id
  and WRI.organization_id (+) = :P_Organization_Id
  and WRI.wip_entity_id (+) = WT.wip_entity_id
 and WRI.line_id (+) = WT.line_id
  &Limit_Classes
  and WL.organization_id (+) = :P_Organization_Id
  and WL.line_id (+) = WT.line_id
  and MSI.organization_id (+) = :P_Organization_Id
  and MSI.inventory_item_id (+) = WE.primary_item_id
  and BD.department_id (+) = WT.department_id
  and BR.resource_id (+) = WA.resource_id
  and CA.activity_id (+) = WA.activity_id
  and LU.lookup_type = 'WIP_TRANSACTION_TYPE_SHORT'
  and LU.lookup_code = WT.transaction_type
  and XLU.lookup_type (+) = 'CST_BASIS_SHORT'
  and XLU.lookup_code (+) = WA.basis_type
  and RLU.lookup_type (+) = 'SYS_YES_NO'
  and RLU.lookup_code (+) = WT.standard_rate_flag
  order by &P_FLEXDATA, WE.wip_entity_name, WL.line_code, WA.transaction_date, 
WT.transaction_type, WT.operation_seq_num, WT.resource_seq_num, 
BR.resource_code, WT.creation_date, WA.transaction_id, WA.ROWID
Parameter Name SQL text Validation
Project_Dummy
 
Number
Category Structure
 
Number
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Quanitity Precision
 
Number
Org_Id
 
Number
Flex Structure Number
 
Number
Project Id
 
LOV Oracle
Category To
 
Category From
 
Category Set
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Class
 
LOV Oracle
Activity
 
LOV Oracle
Department
 
LOV Oracle
Transaction Type
 
LOV Oracle
Assembly
 
Line
 
LOV Oracle
Job/Schedule
 
LOV Oracle
GL Batch
 
LOV Oracle
Account
 
To
 
Date
Dates From
 
Date