Tech Trainings

Sample Test

October 17, 2024 by admin

select
    INVOICE_ID,
	Voucher_Number,
	source INVOICE_SOURCE_NAME,
	Supplier_Number,
	Supplier_Name,
	invoice_Number,
	Creation_Date Invoice_Date,
	Invoice_Amount,
	APPROVAL_STATUS, 
	WFAPPROVAL_STATUS Workflow_Approval_Status,
 

	Approval_Action_Date,
	Approval_Action_By,
	Approver_Emplid,
	Approval_Status_Description  
	
		   ,LISTAGG((
			SELECT
					COMMENTS.WFCOMMENT
			FROM
					FA_FUSION_SOAINFRA.WFCOMMENTS	COMMENTS
				   ,FA_FUSION_SOAINFRA.WFTASK		TASKS
			WHERE
					COMMENTS.TASKID			=	TASKS.TASKID
				AND TASKS.IDENTIFICATIONKEY	=	TO_CHAR(INVOICE_ID)
				--AND COMMENTS.UPDATEDBY		=	PAPF.PERSON_NUMBER
	    ), ', ')
		 aWFCOMMENT
		
		

FROM
(
	select distinct
	AI.INVOICE_ID,
	ai.source,
	PS.SEGMENT1 Supplier_Number,
	PS.VENDOR_NAME Supplier_Name,
	AI.INVOICE_NUM Invoice_Number,
	NVL(to_char(AI.DOC_SEQUENCE_VALUE),to_char(AI.VOUCHER_NUM)) Voucher_Number,
	AI.INVOICE_AMOUNT Invoice_Amount,
	AI.CREATION_DATE Creation_Date,
	NVL(aia.action_date,aia.LAST_UPDATE_DATE) Approval_Action_Date,
	ppn.full_name Approval_Action_By,
	AIA.APPROVER_ID Approver_Emplid,
	AIA.RESPONSE Approval_Status_Description ,
	ai.APPROVAL_STATUS ,
	ai.WFAPPROVAL_STATUS
	from
	AP_INVOICES_ALL AI,
	POZ_SUPPLIERS_V PS,
	AP_INV_APRVL_HIST_ALL AIA,
	PER_PERSON_NAMES_F ppn,
	per_users pu
	where AI.VENDOR_ID = PS.VENDOR_ID
	and aia.approver_id=pu.username
	and pu.person_id = ppn.person_id
	and ppn.name_type='GLOBAL'
	and ai.invoice_id=AIA.invoice_id
--	and ai.APPROVAL_STATUS = 'APPROVED'
	and NVL(aia.action_date,aia.LAST_UPDATE_DATE) BETWEEN :P_APPROVAL_DATE_FROM AND :P_APPROVAL_DATE_TO
	and (AI.DOC_SEQUENCE_VALUE IN (:p_voucher_number) OR 'All' IN (:p_voucher_number) OR 'ALL' IN (:p_voucher_number||'ALL') )

UNION

	select distinct
		AI.INVOICE_ID,
		ai.source,
	PS.SEGMENT1 Supplier_Number,
	PS.VENDOR_NAME Supplier_Name,
	AI.INVOICE_NUM Invoice_Number,
	NVL(to_char(AI.DOC_SEQUENCE_VALUE),to_char(AI.VOUCHER_NUM)) Voucher_Number,
	AI.INVOICE_AMOUNT Invoice_Amount,
	AI.CREATION_DATE Creation_Date,
	NVL(aia.action_date,aia.LAST_UPDATE_DATE) Approval_Action_Date,
	Null Approval_Action_By,
	AIA.APPROVER_ID Approver_Emplid,
	AIA.RESPONSE Approval_Status_Description,
	ai.APPROVAL_STATUS ,
	ai.WFAPPROVAL_STATUS
	from
	AP_INVOICES_ALL AI,
	POZ_SUPPLIERS_V PS,
	AP_INV_APRVL_HIST_ALL AIA,
	per_users pu
	where AI.VENDOR_ID = PS.VENDOR_ID
	and aia.approver_id=pu.username
	and pu.person_id is null
	and ai.invoice_id=AIA.invoice_id
--	and ai.APPROVAL_STATUS = 'APPROVED'
	and NVL(aia.action_date,aia.LAST_UPDATE_DATE) BETWEEN :P_APPROVAL_DATE_FROM AND :P_APPROVAL_DATE_TO
	and (AI.DOC_SEQUENCE_VALUE IN (:p_voucher_number) OR 'ALL' IN (:p_voucher_number) OR 'ALL' IN (:p_voucher_number||'ALL') )

)results

WHERE 1=1

GROUP BY 
    INVOICE_ID,
	Voucher_Number,
	source  ,
	Supplier_Number,
	Supplier_Name,
	invoice_Number,
	Creation_Date  ,
	Invoice_Amount,
	APPROVAL_STATUS, 
	WFAPPROVAL_STATUS  ,
 

	Approval_Action_Date,
	Approval_Action_By,
	Approver_Emplid,
	Approval_Status_Description  
	
	
order by
invoice_id,
invoice_Number,
Approval_Action_Date

Comments |0|