VIEW: APPS.JAI_ST_FORMS_DTL_GRPISS_V

Source

View Text - Preformatted

SELECT b.st_hdr_id , b.header_id , /* added for FP bug# 5636961 */ decode(a.party_type_flag,'V',Null,'O',b.rcv_transaction_id) rcv_transaction_id , /* added for FP bug# 5636961 */ a.form_type , b.invoice_id , b.issue_receipt_flag , b.po_num , b.doc_type , sum(nvl(b.tax_target_amount,0)) inv_tax_target_amt , sum(nvl(b.matched_amount,0)) inv_matched_amt FROM JAI_CMN_STFORM_HDRS_ALL a , JAI_CMN_ST_FORM_DTLS b , JAI_CMN_TAXES_ALL c WHERE a.st_hdr_id = b.st_hdr_id AND b.tax_id = c.tax_id AND a.form_type = c.stform_type AND b.issue_receipt_flag = 'I' AND ( ( a.party_type_flag = 'V' AND EXISTS ( SELECt 1 FROM ap_invoices_all d WHERE b.invoice_id = d.invoice_id AND d.cancelled_by IS NULL AND d.cancelled_amount IS NULL ) ) OR a.party_type_flag = 'O' ) GROUP BY b.st_hdr_id , b.header_id , /* added for fp bug 5636961*/ decode(a.party_type_flag , 'V',Null,'O',b.rcv_transaction_id) , /*5636961 */ a.form_type , b.invoice_id , b.issue_receipt_flag , b.po_num , b.doc_type

View Text - HTML Formatted

SELECT B.ST_HDR_ID
, B.HEADER_ID
, /* ADDED FOR FP BUG# 5636961 */ DECODE(A.PARTY_TYPE_FLAG
, 'V'
, NULL
, 'O'
, B.RCV_TRANSACTION_ID) RCV_TRANSACTION_ID
, /* ADDED FOR FP BUG# 5636961 */ A.FORM_TYPE
, B.INVOICE_ID
, B.ISSUE_RECEIPT_FLAG
, B.PO_NUM
, B.DOC_TYPE
, SUM(NVL(B.TAX_TARGET_AMOUNT
, 0)) INV_TAX_TARGET_AMT
, SUM(NVL(B.MATCHED_AMOUNT
, 0)) INV_MATCHED_AMT
FROM JAI_CMN_STFORM_HDRS_ALL A
, JAI_CMN_ST_FORM_DTLS B
, JAI_CMN_TAXES_ALL C
WHERE A.ST_HDR_ID = B.ST_HDR_ID
AND B.TAX_ID = C.TAX_ID
AND A.FORM_TYPE = C.STFORM_TYPE
AND B.ISSUE_RECEIPT_FLAG = 'I'
AND ( ( A.PARTY_TYPE_FLAG = 'V'
AND EXISTS ( SELECT 1
FROM AP_INVOICES_ALL D
WHERE B.INVOICE_ID = D.INVOICE_ID
AND D.CANCELLED_BY IS NULL
AND D.CANCELLED_AMOUNT IS NULL ) ) OR A.PARTY_TYPE_FLAG = 'O' ) GROUP BY B.ST_HDR_ID
, B.HEADER_ID
, /* ADDED FOR FP BUG 5636961*/ DECODE(A.PARTY_TYPE_FLAG
, 'V'
, NULL
, 'O'
, B.RCV_TRANSACTION_ID)
, /*5636961 */ A.FORM_TYPE
, B.INVOICE_ID
, B.ISSUE_RECEIPT_FLAG
, B.PO_NUM
, B.DOC_TYPE