WIP Resource Transaction

Description
Categories: BI Publisher
Application: Work in Process
Source: Resource Transaction Report (XML)
Short Name: WIPTRTXN_XML
DB package: WIP_WIPTRTXN_XMLP_PKG
SELECT &P_FLEXDATA C_FLEXDATA
,      DECODE(:P_SORT,1,&P_FLEXDATA,'X') C_FLEXDATA_MASTER
,      WT.TRANSACTION_DATE
,      DECODE(:P_SORT,2,WT.TRANSACTION_DATE,SYSDATE) DATE_MASTER
,      BR.RESOURCE_CODE
,      WE.WIP_ENTITY_NAME
,      WL.LINE_CODE
,      WT.OPERATION_SEQ_NUM
,      WT.RESOURCE_SEQ_NUM
,      BD.DEPARTMENT_CODE
,      WT.TRANSACTION_QUANTITY
,      WT.TRANSACTION_UOM
,      WT.PRIMARY_QUANTITY
,      WT.PRIMARY_UOM
,      CA.ACTIVITY
,      NVL(HRE.FULL_NAME,'  ')
,      DECODE(:P_SORT,3,NVL(HRE.FULL_NAME,'  '),'X') EMP_MASTER
,      NVL(WE.PRIMARY_ITEM_ID,-1) ITEM_ID
,      DECODE(:P_SORT,1,NVL(WE.PRIMARY_ITEM_ID,-1),1) ITEM_ID_MASTER
,      MTR.REASON_NAME
,      WT.REFERENCE
,      MSI.DESCRIPTION
,      DECODE(:P_SORT,1,MSI.DESCRIPTION,'X') DESC_MASTER, 
	NVL2(MSI.inventory_item_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_master_sort', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'),NULL) C_FLEX_MASTER_SORT, 
	NVL2(MSI.inventory_item_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assembly_master', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),NULL) C_ASSEMBLY_MASTER, 
	NVL2(MSI.inventory_item_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assembly2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),NULL) C_ASSEMBLY2
FROM   MTL_SYSTEM_ITEMS_VL MSI
,      BOM_RESOURCES BR
,      BOM_DEPARTMENTS BD
,      WIP_ENTITIES WE
,      WIP_LINES WL
,      CST_ACTIVITIES CA
,      PER_PEOPLE_F HRE
,      MTL_TRANSACTION_REASONS MTR
,      WIP_TRANSACTIONS WT
WHERE  WT.ORGANIZATION_ID = :P_ORGANIZATION_ID  
&C_Limit_Dates
AND    BR.RESOURCE_ID = WT.RESOURCE_ID
&C_Limit_Resource
AND    BD.DEPARTMENT_ID = WT.DEPARTMENT_ID
AND    WE.WIP_ENTITY_ID = WT.WIP_ENTITY_ID
AND    WE.ORGANIZATION_ID = :P_ORGANIZATION_ID 
&C_Limit_Jobs
AND    WL.LINE_ID(+) = WT.LINE_ID
AND    WL.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID 
&C_Limit_Lines
AND    CA.ACTIVITY_ID(+) = WT.ACTIVITY_ID 
&C_Limit_Activities
AND    HRE.PERSON_ID(+) = WT.EMPLOYEE_ID 
&C_Limit_Emp
AND    MTR.REASON_ID(+) = WT.REASON_ID 
&C_Limit_Reasons
AND    MSI.INVENTORY_ITEM_ID(+) = WE.PRIMARY_ITEM_ID
AND    MSI.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID 
AND   TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE(+) AND HRE.EFFECTIVE_END_DATE(+)
--&C_Limit_Assemblies 
and &P_FLEXWHERE 
ORDER BY C_FLEX_MASTER_SORT,17 ASC,5 ASC , 
/*to_date ( 
  to_char ( WT.TRANSACTION_DATE ) , 'DD-MON-RR HH24:MI' ) , */
  to_char ( WT.TRANSACTION_DATE ,'DD-MON-RR HH24:MI'),   --removed to_date to fix the bug 10273317
  WE.WIP_ENTITY_NAME , WL.LINE_CODE , WT.OPERATION_SEQ_NUM , 
  WT.RESOURCE_SEQ_NUM , WT.CREATION_DATE , WT.TRANSACTION_ID
/*ORDER BY  to_date(to_char (WT.TRANSACTION_DATE), 'DD-MON-RR HH24:MI')
,           WE.WIP_ENTITY_NAME
,           WL.LINE_CODE
,           WT.OPERATION_SEQ_NUM
,           WT.RESOURCE_SEQ_NUM
,           WT.CREATION_DATE
,           WT.TRANSACTION_ID*/
Parameter Name SQL text Validation
Sort By
 
LOV Oracle
Show Details
 
LOV Oracle
Jobs/Schedules From
 
LOV Oracle
To
 
LOV Oracle
Resources From
 
LOV Oracle
To
 
LOV Oracle
Lines From
 
LOV Oracle
To
 
LOV Oracle
Assemblies From
 
To
 
Dates From
 
Date
To
 
Date
Employees From
 
LOV Oracle
To
 
LOV Oracle
Reasons From
 
LOV Oracle
To
 
LOV Oracle
Activities From
 
LOV Oracle
To
 
LOV Oracle
Org_id
 
Number
Quantity Precision
 
Number