Hello,

2a, scenario where the invoices are paid in real life but the total billing amount on the project is needed.

Enter billing events for the invoiced and PAID amounts.

Generate invoices, approve and release.

Option 1: Run PRC Interface Invoices to Receivables - the stand alone job, NOT the streamline. This will put the invoices in the AR invoice interface. Use sql to delete them from the interface since you don't want them in Receivables. Use sql to update the pa draft invoices table to set the transfer status code to A and the ra invoice number to -123. Status code A is needed so that Projects won't keep trying to interface the invoices. Train the users that -123 as an invoice number means that the invoices are converted ones. Projects now has the total previously billed and AR has no clutter.

Option 2: Run the streamline to interface the invoices to Receivables and tie back to Projects. Enter one large credit memo in Receivables and use it to zero out the invoices, or use Adjustments to zero them out. Projects and AR both have the total invoicing, and AR has the credits/adjustments to indicate they had been paid.

Which option you choose may depend on volume. If there are only 6 invoices, option 2 is nice and clean. If you had hundreds of invoices, such as converting into Oracle from a legacy system, option 1 is faster.

2b, scenario where the invoice is UNPAID. After processing the fully paid invoices as described above, enter billing events for the unpaid amounts. Generate invoices, approve, and release. If you are using manual invoice numbering, give the invoices the same number they had in the legacy system. If you are using automatic invoice numbering, add a comment on the invoice to indicate the original invoice number, or include the original invoice number in the event description; the point is to be able to match from one system to the other. Run the streamline to Receivables and tie back to Projects. The invoice amounts are now in Projects and in AR, and you can use the AR Invoice button on the Projects Invoice Review form to jump from the projects invoice to the receivables one.

Hi

When you need Bank and Credit Card, what exactly you want ?

Because credit card and bank are attached to a customer as well as site level

You can use the following sql (Please modify it as per your requirement and do not consider it as final )

SELECT  cust.account_number,cust.account_name,p.cust_account_id,

p.ACCT_SITE_USE_ID,  u.instrument_type instrument_type,

c.masked_cc_number ,

decode(i.card_issuer_code, NULL, ccunk.meaning, i.card_issuer_name) account_type,

u.instrument_id ,

NVL(c.chname,hzcc.party_name) account_holder,

c.card_issuer_code card_code,

u.instrument_payment_use_id instr_assignment_id

FROM    fnd_lookup_values_vl ccunk,

  iby_creditcard c,

  iby_creditcard_issuers_vl i,

  iby_external_payers_all p,

  iby_pmt_instr_uses_all u,

  hz_parties hzcc,

    hz_cust_accounts cust,

  hz_party_site_uses psu,

  hz_party_sites hps,

  hz_locations loc,

  fnd_territories_vl terr

WHERE p.cust_account_id = cust.cust_account_id

AND p.party_id = cust.party_id

AND u.ext_pmt_party_id = p.ext_payer_id

AND u.instrument_type = 'CREDITCARD'

AND u.payment_flow = 'FUNDS_CAPTURE'

AND nvl(TRUNC(u.start_date), sysdate - 1)  <= TRUNC(sysdate)

AND nvl(TRUNC(u.end_date),   sysdate + 10) >= TRUNC(sysdate)

AND u.instrument_id = c.instrid(+)

AND nvl(c.inactive_date,   sysdate + 10) > sysdate

AND c.card_issuer_code = i.card_issuer_code(+)

AND c.card_owner_id = hzcc.party_id(+)

AND c.addressid = psu.party_site_use_id(+)

AND psu.party_site_id = hps.party_site_id(+)

AND hps.location_id = loc.location_id(+)

AND loc.country = terr.territory_code(+)

AND ccunk.lookup_type = 'IBY_CARD_TYPES'

AND ccunk.lookup_code = 'UNKNOWN';       

Best Regards

Bharat

****If my response assisted you on this matter, please feel free to click on the Correct Answer and/or helpful  button, in order to assist community and its users. ***

Hi

Whether earlier query helped ?
It take lot of efforts to write and prepare such queries, I don't have anything handy at the moment.

Best Regards

Bharat