APPS.PSA_AR_GL_INTERFACE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 21

        Select status
        From psa_implementation_all
        Where org_id = c_org_id;

Line: 66

	UPDATE GL_INTERFACE
	   SET ussgl_transaction_code = NULL
	 WHERE user_je_source_name    = 'Receivables'
	   AND set_of_books_id	      = l_sob_id
   	   AND group_id               = l_group_id
	   AND ussgl_transaction_code IS NOT NULL
           AND ( (reference29 = 'MISC_CASH' AND reference30 = 'AR_CASH_RECEIPT_HISTORY')
  	         OR
                 (reference29 IN ('INV_REC','CB_REC','CM_REC','DM_REC','TRADE_CASH','TRADE_UNAPP','ADJ_REC'))
		 OR
		 ( arp_global.sysparam.accounting_method = 'CASH' AND
		   reference29 = 'TRADE_APP' 			  AND
		   reference30 = 'AR_RECEIVABLE_APPLICATIONS' ) );

Line: 81

           psa_utils.debug_other_string(g_state_level,l_full_path,' UPDATE GL_INTERFACE ## 1 -->'
                                                                  || SQL%ROWCOUNT );

Line: 86

	-- Bug 2805101: Update TC from cash receipt header on TRADE_UNAPP
	--              rows for unapplied receipts.
	--

	UPDATE GL_INTERFACE gl
	   SET gl.ussgl_transaction_code =
	   	( SELECT ussgl_transaction_code
	   	    FROM ar_cash_receipts cr
	   	   WHERE cr.cash_receipt_id = TO_NUMBER(SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1)))
	 WHERE gl.user_je_source_name    = 'Receivables'
	   AND gl.set_of_books_id	 = l_sob_id
   	   AND gl.group_id             	 = l_group_id
	   AND gl.reference29		 = 'TRADE_UNAPP'
	   AND gl.reference30		 = 'AR_RECEIVABLE_APPLICATIONS'
	   AND EXISTS
	   	( SELECT 'Cash Receipt Unapplied'
	   	    FROM ar_cash_receipts ar
	   	   WHERE ar.cash_receipt_id = TO_NUMBER(SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1))
	   	     AND status = 'UNAPP' );

Line: 107

           psa_utils.debug_other_string(g_state_level,l_full_path,' UPDATE GL_INTERFACE ## 2 -->'
                                                                  || SQL%ROWCOUNT );

Line: 112

	-- Bug 2805101: Update TC from cash receipt header on TRADE_UNAPP
	--              rows if receipt is applied after the unapplied receipt
	--              has been transferred previously.
	--

	UPDATE GL_INTERFACE gl
	   SET gl.ussgl_transaction_code =
	   	( SELECT ussgl_transaction_code
	   	    FROM ar_cash_receipts cr
	   	   WHERE cr.cash_receipt_id = TO_NUMBER(SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1)))
	 WHERE gl.user_je_source_name    = 'Receivables'
	   AND gl.set_of_books_id	 = l_sob_id
   	   AND gl.group_id             	 = l_group_id
	   AND gl.reference29		 = 'TRADE_UNAPP'
	   AND gl.reference30		 = 'AR_RECEIVABLE_APPLICATIONS'
	   AND NOT EXISTS
	   	( SELECT 'Cash Receipt Unapplied'
	   	    FROM ar_cash_receipts ar
	   	   WHERE ar.cash_receipt_id = TO_NUMBER(SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1))
	   	     AND status = 'UNAPP' )
	   AND NOT EXISTS
	          	( SELECT 'Receipt Applied In This Posting Run'
	          	    FROM gl_interface ar
	          	   WHERE ar.user_je_source_name	= 'Receivables'
			     AND ar.set_of_books_id	= l_sob_id
			     AND ar.group_id            = l_group_id
			     AND ar.reference29		= 'TRADE_CASH'
			     AND ar.reference30		= 'AR_CASH_RECEIPT_HISTORY'
			     AND SUBSTR(ar.reference22, 1, INSTR(ar.reference22, 'C')-1) =
			     		SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1) );

Line: 145

           psa_utils.debug_other_string(g_state_level,l_full_path,' UPDATE GL_INTERFACE ## 3 -->'
                                                                  || SQL%ROWCOUNT );

Line: 162

        	UPDATE GL_INTERFACE
        	   SET USSGL_TRANSACTION_CODE = NULL
        	 WHERE user_je_source_name    = 'Receivables'
		   AND set_of_books_id	      = l_sob_id
		   AND group_id               = l_group_id
       	 	   AND ussgl_transaction_code IS NOT NULL
        	   AND reference29     = 'TRADE_REC'
                   AND reference30     = 'AR_RECEIVABLE_APPLICATIONS'
		   AND is_mfar_transaction
		   		(TO_NUMBER(SUBSTR(reference22, INSTR(reference22, 'C')+1)), l_sob_id) = 'Y';

Line: 174

                  psa_utils.debug_other_string(g_state_level,l_full_path,' UPDATE GL_INTERFACE ## 4 -->'
                                                                  || SQL%ROWCOUNT );

Line: 227

   Select applied_customer_trx_id
   From ar_receivable_applications
   Where receivable_application_id = c_doc_id;