eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IGI_RPI_STANDING_CHARGES_V

Source

View Text - Preformatted


SELECT jursc.charge_reference, jursc.status, nvl(jursc.term_id, 4) term_id, jursc.standing_charge_date, rctt.name transaction_type_name, rt.name term_id_name, jursc.currency_code, rbs.name batch_source_name, rs.name salesperson, substr(partyb.party_name,1,50) bill_to_customer, cust_acctb.account_number bill_to_number, locb.address1 bill_address1, locb.address2 bill_address2, locb.address3 bill_address3, decode(jursc.bill_to_contact_id, '', '', substr(partybc.person_last_name,1,50) || ',' || ' ' || substr(partybc.person_first_name,1,50)) bill_contact, substr(partys.party_name,1,50) ship_to_customer, cust_accts.account_number ship_to_number, locs.address1 ship_address1, locs.address2 ship_address2, locs.address3 ship_address3, decode(jursc.ship_to_contact_id, '', '', substr(partysc.person_last_name,1,50) || ',' || ' ' || substr(partysc.person_first_name,1,50)) ship_contact, jursc.description, jursc.comments, jursc.start_date, jursc.end_date, jursc.period_name, jursc.advance_arrears_ind, jursc.next_due_date, jursc.reminder_days, jursc.review_date, jursc.suppress_inv_print, arm.name receipt_method_name, jursc.set_of_books_id, jursc.cust_trx_type_id, jursc.generate_sequence, jursc.standing_charge_id, jursc.salesrep_id, jursc.batch_source_id, jursc.bill_to_customer_id, jursc.ship_to_customer_id, jursc.bill_to_address_id, jursc.ship_to_address_id, jursc.bill_to_contact_id, jursc.ship_to_contact_id, jursc.receipt_method_id, jursc.bank_account_id, jursc.bill_to_site_use_id, jursc.ship_to_site_use_id, cust_acctb.cust_account_id, jursc.previous_due_date, jursc.last_update_date, jursc.creation_date, jursc.last_updated_by, jursc.created_by, jursc.last_update_login, jursc.rowid row_id, jurcp.component, jursc.default_invoicing_rule, jursc.org_id, jursc.legal_entity_id, xlo.legal_entity_name, hop.name, jursc.payment_trxn_extension_id FROM ra_salesreps_all rs, ra_cust_trx_types_all rctt, hz_parties partyb, hz_cust_accounts_all cust_acctb, hz_party_sites party_siteb, hz_locations locb, hz_cust_acct_sites_all acct_siteb, hz_cust_account_roles acct_roleb, hz_relationships relb, hz_parties partybc, hz_org_contacts org_countb, ar_receipt_methods arm, igi_rpi_standing_charges jursc, ra_batch_sources_all rbs, igi_rpi_component_periods jurcp, ra_terms_vl rt, xle_le_ou_ledger_v xlo, hr_operating_units hop, hz_parties partys, hz_cust_accounts_all cust_accts, hz_party_sites party_sites, hz_locations locs, hz_cust_acct_sites_all acct_sites, hz_cust_account_roles acct_roles, hz_relationships rels, hz_parties partysc, hz_org_contacts org_conts WHERE jursc.cust_trx_type_id = rctt.cust_trx_type_id AND jursc.salesrep_id = rs.salesrep_id AND jursc.legal_entity_id = xlo.legal_entity_id AND jursc.org_id = hop.organization_id AND jursc.batch_source_id = rbs.batch_source_id AND jursc.org_id = rctt.org_id AND jursc.org_id = rbs.org_id AND jursc.org_id = rs.org_id AND jursc.org_id = xlo.operating_unit_id AND rbs.batch_source_type = 'FOREIGN' AND((rt.term_id = jursc.term_id AND jursc.term_id IS NOT NULL) OR (rt.term_id = 4 AND jursc.term_id IS NULL)) AND partyb.party_id = cust_acctb.party_id AND cust_acctb.cust_account_id=acct_siteb.cust_account_id AND acct_siteb.party_site_id = party_siteb.party_site_id AND locb.location_id = party_siteb.location_id AND jursc.bill_to_customer_id = cust_acctb.cust_account_id AND jursc.bill_to_address_id = acct_siteb.cust_acct_site_id AND acct_roleb.party_id = relb.party_id(+) AND partybc.party_id(+) = relb.subject_id AND relb.relationship_id = org_countb.party_relationship_id(+) AND acct_roleb.role_type(+) = 'CONTACT' AND relb.object_table_name(+) = 'HZ_PARTIES' AND relb.subject_type(+) = 'PERSON' AND relb.subject_table_name(+) = 'HZ_PARTIES' AND jursc.bill_to_contact_id = acct_roleb.cust_account_role_id(+) AND jursc.period_name = jurcp.period_name AND jursc.receipt_method_id = arm.receipt_method_id(+) AND partys.party_id(+) = cust_accts.party_id AND acct_sites.party_site_id = party_sites.party_site_id(+) AND locs.location_id(+) = party_sites.location_id AND acct_roles.party_id = rels.party_id(+) AND rels.party_id = org_conts.party_relationship_id(+) AND acct_roles.role_type(+) = 'CONTACT' AND partysc.party_id(+) = rels.subject_id AND cust_accts.cust_account_id(+) = acct_sites.cust_account_id AND jursc.ship_to_customer_id = acct_sites.cust_account_id(+) AND jursc.ship_to_address_id = acct_sites.cust_acct_site_id(+) AND jursc.ship_to_contact_id = acct_roles.cust_account_role_id(+) AND rels.object_table_name(+) ='HZ_PARTIES' AND rels.subject_type(+) ='PERSON' AND rels.subject_table_name(+) ='HZ_PARTIES'

View Text - HTML Formatted

SELECT JURSC.CHARGE_REFERENCE
, JURSC.STATUS
, NVL(JURSC.TERM_ID
, 4) TERM_ID
, JURSC.STANDING_CHARGE_DATE
, RCTT.NAME TRANSACTION_TYPE_NAME
, RT.NAME TERM_ID_NAME
, JURSC.CURRENCY_CODE
, RBS.NAME BATCH_SOURCE_NAME
, RS.NAME SALESPERSON
, SUBSTR(PARTYB.PARTY_NAME
, 1
, 50) BILL_TO_CUSTOMER
, CUST_ACCTB.ACCOUNT_NUMBER BILL_TO_NUMBER
, LOCB.ADDRESS1 BILL_ADDRESS1
, LOCB.ADDRESS2 BILL_ADDRESS2
, LOCB.ADDRESS3 BILL_ADDRESS3
, DECODE(JURSC.BILL_TO_CONTACT_ID
, ''
, ''
, SUBSTR(PARTYBC.PERSON_LAST_NAME
, 1
, 50) || '
, ' || ' ' || SUBSTR(PARTYBC.PERSON_FIRST_NAME
, 1
, 50)) BILL_CONTACT
, SUBSTR(PARTYS.PARTY_NAME
, 1
, 50) SHIP_TO_CUSTOMER
, CUST_ACCTS.ACCOUNT_NUMBER SHIP_TO_NUMBER
, LOCS.ADDRESS1 SHIP_ADDRESS1
, LOCS.ADDRESS2 SHIP_ADDRESS2
, LOCS.ADDRESS3 SHIP_ADDRESS3
, DECODE(JURSC.SHIP_TO_CONTACT_ID
, ''
, ''
, SUBSTR(PARTYSC.PERSON_LAST_NAME
, 1
, 50) || '
, ' || ' ' || SUBSTR(PARTYSC.PERSON_FIRST_NAME
, 1
, 50)) SHIP_CONTACT
, JURSC.DESCRIPTION
, JURSC.COMMENTS
, JURSC.START_DATE
, JURSC.END_DATE
, JURSC.PERIOD_NAME
, JURSC.ADVANCE_ARREARS_IND
, JURSC.NEXT_DUE_DATE
, JURSC.REMINDER_DAYS
, JURSC.REVIEW_DATE
, JURSC.SUPPRESS_INV_PRINT
, ARM.NAME RECEIPT_METHOD_NAME
, JURSC.SET_OF_BOOKS_ID
, JURSC.CUST_TRX_TYPE_ID
, JURSC.GENERATE_SEQUENCE
, JURSC.STANDING_CHARGE_ID
, JURSC.SALESREP_ID
, JURSC.BATCH_SOURCE_ID
, JURSC.BILL_TO_CUSTOMER_ID
, JURSC.SHIP_TO_CUSTOMER_ID
, JURSC.BILL_TO_ADDRESS_ID
, JURSC.SHIP_TO_ADDRESS_ID
, JURSC.BILL_TO_CONTACT_ID
, JURSC.SHIP_TO_CONTACT_ID
, JURSC.RECEIPT_METHOD_ID
, JURSC.BANK_ACCOUNT_ID
, JURSC.BILL_TO_SITE_USE_ID
, JURSC.SHIP_TO_SITE_USE_ID
, CUST_ACCTB.CUST_ACCOUNT_ID
, JURSC.PREVIOUS_DUE_DATE
, JURSC.LAST_UPDATE_DATE
, JURSC.CREATION_DATE
, JURSC.LAST_UPDATED_BY
, JURSC.CREATED_BY
, JURSC.LAST_UPDATE_LOGIN
, JURSC.ROWID ROW_ID
, JURCP.COMPONENT
, JURSC.DEFAULT_INVOICING_RULE
, JURSC.ORG_ID
, JURSC.LEGAL_ENTITY_ID
, XLO.LEGAL_ENTITY_NAME
, HOP.NAME
, JURSC.PAYMENT_TRXN_EXTENSION_ID
FROM RA_SALESREPS_ALL RS
, RA_CUST_TRX_TYPES_ALL RCTT
, HZ_PARTIES PARTYB
, HZ_CUST_ACCOUNTS_ALL CUST_ACCTB
, HZ_PARTY_SITES PARTY_SITEB
, HZ_LOCATIONS LOCB
, HZ_CUST_ACCT_SITES_ALL ACCT_SITEB
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLEB
, HZ_RELATIONSHIPS RELB
, HZ_PARTIES PARTYBC
, HZ_ORG_CONTACTS ORG_COUNTB
, AR_RECEIPT_METHODS ARM
, IGI_RPI_STANDING_CHARGES JURSC
, RA_BATCH_SOURCES_ALL RBS
, IGI_RPI_COMPONENT_PERIODS JURCP
, RA_TERMS_VL RT
, XLE_LE_OU_LEDGER_V XLO
, HR_OPERATING_UNITS HOP
, HZ_PARTIES PARTYS
, HZ_CUST_ACCOUNTS_ALL CUST_ACCTS
, HZ_PARTY_SITES PARTY_SITES
, HZ_LOCATIONS LOCS
, HZ_CUST_ACCT_SITES_ALL ACCT_SITES
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLES
, HZ_RELATIONSHIPS RELS
, HZ_PARTIES PARTYSC
, HZ_ORG_CONTACTS ORG_CONTS
WHERE JURSC.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
AND JURSC.SALESREP_ID = RS.SALESREP_ID
AND JURSC.LEGAL_ENTITY_ID = XLO.LEGAL_ENTITY_ID
AND JURSC.ORG_ID = HOP.ORGANIZATION_ID
AND JURSC.BATCH_SOURCE_ID = RBS.BATCH_SOURCE_ID
AND JURSC.ORG_ID = RCTT.ORG_ID
AND JURSC.ORG_ID = RBS.ORG_ID
AND JURSC.ORG_ID = RS.ORG_ID
AND JURSC.ORG_ID = XLO.OPERATING_UNIT_ID
AND RBS.BATCH_SOURCE_TYPE = 'FOREIGN' AND((RT.TERM_ID = JURSC.TERM_ID
AND JURSC.TERM_ID IS NOT NULL) OR (RT.TERM_ID = 4
AND JURSC.TERM_ID IS NULL))
AND PARTYB.PARTY_ID = CUST_ACCTB.PARTY_ID
AND CUST_ACCTB.CUST_ACCOUNT_ID=ACCT_SITEB.CUST_ACCOUNT_ID
AND ACCT_SITEB.PARTY_SITE_ID = PARTY_SITEB.PARTY_SITE_ID
AND LOCB.LOCATION_ID = PARTY_SITEB.LOCATION_ID
AND JURSC.BILL_TO_CUSTOMER_ID = CUST_ACCTB.CUST_ACCOUNT_ID
AND JURSC.BILL_TO_ADDRESS_ID = ACCT_SITEB.CUST_ACCT_SITE_ID
AND ACCT_ROLEB.PARTY_ID = RELB.PARTY_ID(+)
AND PARTYBC.PARTY_ID(+) = RELB.SUBJECT_ID
AND RELB.RELATIONSHIP_ID = ORG_COUNTB.PARTY_RELATIONSHIP_ID(+)
AND ACCT_ROLEB.ROLE_TYPE(+) = 'CONTACT'
AND RELB.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RELB.SUBJECT_TYPE(+) = 'PERSON'
AND RELB.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND JURSC.BILL_TO_CONTACT_ID = ACCT_ROLEB.CUST_ACCOUNT_ROLE_ID(+)
AND JURSC.PERIOD_NAME = JURCP.PERIOD_NAME
AND JURSC.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID(+)
AND PARTYS.PARTY_ID(+) = CUST_ACCTS.PARTY_ID
AND ACCT_SITES.PARTY_SITE_ID = PARTY_SITES.PARTY_SITE_ID(+)
AND LOCS.LOCATION_ID(+) = PARTY_SITES.LOCATION_ID
AND ACCT_ROLES.PARTY_ID = RELS.PARTY_ID(+)
AND RELS.PARTY_ID = ORG_CONTS.PARTY_RELATIONSHIP_ID(+)
AND ACCT_ROLES.ROLE_TYPE(+) = 'CONTACT'
AND PARTYSC.PARTY_ID(+) = RELS.SUBJECT_ID
AND CUST_ACCTS.CUST_ACCOUNT_ID(+) = ACCT_SITES.CUST_ACCOUNT_ID
AND JURSC.SHIP_TO_CUSTOMER_ID = ACCT_SITES.CUST_ACCOUNT_ID(+)
AND JURSC.SHIP_TO_ADDRESS_ID = ACCT_SITES.CUST_ACCT_SITE_ID(+)
AND JURSC.SHIP_TO_CONTACT_ID = ACCT_ROLES.CUST_ACCOUNT_ROLE_ID(+)
AND RELS.OBJECT_TABLE_NAME(+) ='HZ_PARTIES'
AND RELS.SUBJECT_TYPE(+) ='PERSON'
AND RELS.SUBJECT_TABLE_NAME(+) ='HZ_PARTIES'